Data Processing Service

ABSTRACT

In general, the subject matter described in this disclosure can be embodied in methods, systems, and program products. A system includes a first data center, a second data center, and a third data center. The multiple data centers are configured to replicate a logical collection of data that comprises multiple logical partitions of data. The system comprises a first writing subsystem that is designated to write updates to a copy of a first logical partition of data that is stored by the first data center. The system comprises a second writing subsystem that is designated to write updates to a copy of a second logical partition of data that is stored by the second data center. The system comprises a third writing subsystem that is designated to write updates to a copy of a third logical partition of data that is stored by the third data center.

CROSS-REFERENCE TO RELATED APPLICATION

This application claims priority to U.S. Provisional Application Ser.No. 61/559,707, filed on Nov. 14, 2011, the entire contents of which arehereby incorporated by reference.

TECHNICAL FIELD

This document generally describes techniques, methods, systems, andmechanisms for providing a data processing service.

BACKGROUND

The present disclosure generally relates to replication of data amongcomputing devices and large-scale analytical data processing. Such dataprocessing has become widespread in web companies and across industries,not least due to low-cost storage that enabled collecting vast amountsof business-critical data. Putting this data at the fingertips ofanalysts and engineers has grown increasingly important; interactiveresponse times often make a qualitative difference in data exploration,monitoring, online customer support, rapid prototyping, debugging ofdata pipelines, and other tasks. Performing interactive data analysis atscale demands a high degree of parallelism. For example, reading oneterabyte of compressed data in one second using today's commodity disksmay require tens of thousands of disks. Similarly, CPU-intensive queriesmay need to run on thousands of cores to complete within seconds.

SUMMARY

A data processing service is herein disclosed. The described serviceprovides a scalable, interactive ad-hoc query system for analysis ofnested data. By combining multi-level execution trees and columnar datalayout, the described system and methods is capable of running rapid andefficient queries such as aggregation queries. A columnar storagerepresentation for nested records, a prevalent data model that may beused in many web-scale and scientific datasets, is described. Inaccordance with an embodiment, a record is decomposed into columnstripes, each column encoded as a set of blocks, each block containingfield values and repetition and definition level information. Levelinformation is generated using a tree of field writers, whose structurematches the field hierarchy in the record schema. The record can beassembled from the columnar data efficiently using a finite statemachine that reads the field values and level information for each fieldand appends the values sequentially to the output records. Compared withtraditional solutions that extract all of the data fields from everyrecord, a finite state machine can be constructed that accesses alimited amount of data fields in all or a portion of the records (e.g.,a single data field in all of the records). Moreover, by storingadditional metadata such as constraint information with the columnarstorage representation, additional types of queries can be supported.

A multi-level serving tree is used to execute queries. In oneembodiment, a root server receives an incoming query, reads metadatafrom the tables, and routes the queries to a next level in the servingtree. Leaf servers communicate with a storage layer or access the dataon local storage, where the stored data can be replicated, and readstripes of nested data in the columnar representation. Each server canhave an internal execution tree corresponding to a physical queryexecution plan, comprising a set of iterators that scan input columnsand emit results of aggregates and scalar functions annotated with levelinformation. In another embodiment, a query dispatcher is provided whichschedules queries based on their priorities and balances the load. Thequery dispatcher also provides fault tolerance when one server becomesmuch slower than others or as a replica becomes unreachable. The querydispatcher can compute a histogram of processing times for executionthreads on the leaf servers and reschedule to another server whenprocessing time takes a disproportionate amount of time.

A web service may provide users remote access to the query system and asupporting data storage system. Users of the web service may upload datato the data storage system for hosted storage. A portion of uploadeddata may include collections of nested records and may be stored as anobject. The web service may provide remote data hosting for multipleusers, allowing the multiple users to stream data to the web service andaggregate the data in a single location. Users may create tables onwhich to perform queries, and may import the data in one or more objectsstored in the data storage system into the tables. The import processcan include converting nested records in an object into columnar data,and storing the columnar data in a different data layer than theobjects. Thus, from a user's perspective, a table may be filled withdata from objects, but actually may instead reference underlying sets ofcolumnar data. In this case, queries of the tables by web service usersmay cause the query system to query particular columns of data thatunderlie the tables.

The columnar data may be queried in situ. Maintaining the columnar dataon a common storage layer and providing mechanisms to assemble recordsfrom the columnar data enables operability with data management toolsthat analyze data in a record structure. The system may scale tonumerous CPUs and be capable of rapidly reading large amounts of data.Particular embodiments can be implemented, in certain instances, torealize one or more of the following advantages. Nested data may beoperated on in situ, such that the data may be accessed without loadingthe data with a database management system. Queries of nested data maybe performed in a reduced execution time than required by other analysisprograms. A columnar storage data structure that is implemented on acommon storage layer enables multiple different analysis programs toaccess the columnar storage data structure.

The details of one or more embodiments are set forth in the accompanyingdrawings and the description below. Other features, objects, andadvantages will be apparent from the description and drawings, and fromthe claims.

DESCRIPTION OF DRAWINGS

FIG. 1 illustrates record-wise v. columnar representation of nesteddata.

FIG. 2 illustrates two sample nested records and their schema.

FIG. 3 illustrates column-striped representations of the sample nestedrecords.

FIG. 4 is an algorithm for dissecting a record into columns.

FIG. 5 illustrates an automaton for performing complete record assembly.

FIG. 6 illustrates an automaton for assembly records from two fields,and the records that the automaton produces.

FIG. 7 is an algorithm for constructing a record assembly automaton.

FIG. 8 is an algorithm for assembling a record from columnar data.

FIG. 9 depicts a sample query that performs projection, selection, andwithin-record aggregation.

FIG. 10 illustrates a system architecture and execution inside a servernode.

FIG. 11 is a table illustrating the datasets used in the experimentalstudy.

FIG. 12 is a graph that illustrates the performance breakdown that mayoccur when reading from a local disk.

FIG. 13 is a graph that illustrates execution of both MapReduce and thedescribed system on columnar v. record-oriented storage.

FIG. 14 is a graph that illustrates the execution time as a function ofserving tree levels for two aggregation queries.

FIG. 15 is a graph that illustrates histograms of processing times.

FIG. 16 is a graph that illustrates execution time when the system isscaled from 1000 to 4000 nodes using a top-k query.

FIG. 17 is a graph that illustrates a percentage of processed tables asa function of processing time per tablet.

FIG. 18 is a graph that illustrates query response time distribution ina monthly workload.

FIG. 19 is a block diagram of a system for generating and processingcolumnar storage representations of nested records.

FIG. 20 is a flow chart of an example process for generating columnardata.

FIG. 21 is a block diagram illustrating an example of a system thatimplements a web service for data storage and processing.

FIG. 22 is a flowchart showing an example of a process for performingdata storage and processing.

FIG. 23 shows a schematic diagram of an example computing systeminfrastructure.

FIG. 24 shows a schematic diagram of an example of data naming structurethat may be used to support data replication.

FIG. 25 shows a schematic diagram of components that may be used toprovide the data processing service.

FIG. 26 shows a schematic diagram of an example of the components storedwithin the global table and how these components relate to auser-generated table.

FIG. 27 is a schematic diagram showing examples of locally storedtables.

FIG. 28 is a flowchart showing an example of a process for reading datafrom a table with a query.

FIG. 29 is a flowchart showing an example of a process for inboundreplication of data.

FIGS. 30A-C show a swim-lane diagram illustrating an example of aprocess for writing and replicating data.

FIG. 31 is a block diagram of computing devices that may be used toimplement the systems and methods described in this document, as eithera client or as a server or plurality of servers.

Like reference symbols in the various drawings indicate like elements.

DETAILED DESCRIPTION

This document describes techniques, methods, systems, and mechanisms fora data storage and processing service. The described system may generateand process columnar storage representations of nested records. As anillustration, an organization may store data from web pages in recordsof nested information. The nested information may be compiled in acolumnar data storage format that enables efficient queries of the datausing a multi-level execution tree. The columnar data may bere-assembled into records for input into analysis programs that operateon record-oriented data.

More specifically, each record may be an instantiation of a schema thatdefines a formatting of records, where the records are created inaccordance with the schema. For example, a schema may identify variousfields for storing information about a web page and a structure fororganizing fields in a record and their corresponding values. When aninstance of a record for describing the characteristics of a web page isgenerated, the record may include for each field a data element and acorresponding value. The data element may define the semantics of thevalue in accordance with a definition in the schema. The term dataelement and field may be used interchangeably in this document. Fieldmay also refer to a combination of a data element and a correspondingvalue.

A particular record may need not include all of the fields that aredefined by a schema. Thus, the schema may serve as a ‘template’ fromwhich fields may be selected for the particular record. For example, theschema may include a field for defining information about video contentin a web page. If a web page does not include video content, then therecord corresponding to the web page may not include the field from theschema that defines information about videos on the web page. Thus, someof the fields may be ‘optional.’

Some of the fields in a record, however, may be ‘required.’ For example,a ‘required’ field in the schema may be a Uniform Resource Locator (URL)of a source location for the document that served the web page. Thefield may be required because every web page document may be retrievedfrom a source location (i.e., there is a URL available for everydocument) and because the field may be required to further processinformation on the web page (e.g., to determine if the content haschanged).

A field may also be ‘repeatable.’ A field that is in the schema and thatis defined as repeatable may be replicated at the location defined bythe schema repeatedly in a instantiation of the schema (i.e., in arecord). For example, a schema may include a field that is for definingdocuments that link to the web page. The schema may only specify thefield a single time, but may indicate that the field is repeatable(e.g., because several documents may link to a particular web page).Thus, a record for the web page may include multiple fields thatidentify a value for a linking web page. The repeated fields may belocated at a same level and nested beneath a parent field in the record(as discussed in more detail below).

The fields of the schema (and thus the fields in the records) may benested. In other words, some fields may be children of other fields,which may be referenced as the parent fields, grandparent fields, etc.In some examples, children nodes are those nodes in the schema that arefound within a pair of opening and closing curly brackets immediatelyfollowing the parent node. Other implementations for nesting, however,may be utilized (e.g., the use of a start tag for the field and an endtag for the field). Thus, except for the fields that are at the highestlevel (e.g., the fields that are not children of any other fields), eachfield may have a parent field.

Nesting may be helpful for organizing information intoconceptually-related chunks of information. Returning to our earlierexample, the schema may include a ‘Video’ field. The ‘Video’ field mayinclude several children fields that may identify the characteristics ofthe video (e.g., how long the video is, the format of the video, and theresolution of the video). Thus, when a record is constructed, childrennodes may not be placed in the record if their parent nodes are notpresent. In other words, a record for a web page that does not include avideo may not include a ‘VideoLength’ field because the record does notinclude a ‘Video’ field (i.e., the parent of the ‘VideoLength’ field).Application programs that enable viewing and editing a record mayvisually nest the dependent children off of the parent children (e.g.,indent the children to the right of the parent field).

Analyzing millions of records may be time consuming. In some examples auser is interested in a data from a single field, but each of therecords must be accessed in its entirety. For example, a user mayrequest that an analysis program check each of millions of records toidentify those records that are associated with web pages that includevideos that are longer than ten minutes and that have a ‘High’resolution. Because each record may be stored as a separate datastructure, each entire record may need to be loaded into a databasemanagement system in order to query the record to determine if therecord includes the particular combination of video length andresolution.

Such a loading of every single record may be prohibitively expensive,both on the quantity of servers that are required to perform the taskand an amount of time necessary to complete the query. Significant timesavings can be obtained by storing all of the values for a particularfield—selected from across the millions of records—together in acontiguous portion of memory. Such storage of values from severalrecords but for a particular field is called columnar storage. Incontrast, the example where information for a particular record isstored contiguously in memory is referred to as record-oriented storage.

Columnar storage for nested records, however, poses unique difficulties.A field in a record may be identified by its path, which may include alisting of the field and the parent fields (e.g.,GrandParent.Parent.Child). Because one or more of the fields in the pathmay be repeating, there may be several instances of a field with thesame path name. Thus, when looking at a consecutive listing of columnardata for a particular field, a mechanism is needed to identify whichvalues belong to which records, and for those records that includemultiple values for a particular path, what is the respective locationof the value in the record. In other words, given a sequence of valuesin a columnar structure, a mechanism is needed to reconstruct thestructure of the record from the values.

The mechanism for reconstructing the structure of a record from columnardata includes storing, for each value in the columnar data, a‘repetition’ level and a ‘definition’ level. Each ‘level’ is a sequenceof bits that represents a number. For example, a ‘level’ of 3 may berepresented by two bits (e.g., ‘11’). In another example, a ‘level’ of 5may be represented by three bits (e.g., ‘101’).

The ‘repetition’ level that is stored for a particular value indicatesthe field in the value's path that has most recently repeated. As anillustration, a column of values may be stored for a field with the path‘Video.Resolution.Width.’ A repetition level of ‘1’ may indicate thatthe ‘Video’ field most recently repeated, while a repetition level of‘2’ may indicate that the ‘Resolution’ field most recently repeated.Recently repeating can indicate, from the position of the value in therecord from which the value was selected and working upwards towards thebeginning of the document, which field in the path‘Video.Resolution.Width’ is the first to reach a count of two (e.g.,which field is encountered for the second time first).

For example, working upwards from the location of the ‘Width’ value,each field is encountered a single time. Finding a second instance ofeach field requires traversing to the depths of the next, adjacentnested field (and possibly to further nestings). Thus, a ‘Video’ fieldmay be encountered that does not include any ‘Resolution’ children(e.g., because the ‘Resolution’ field is optional or a repeating field).Thus, the ‘Video’ field has been encountered a second time and is thusthe most recently repeated field. A repetition level of ‘1’ is assignedto the value.

A repetition level of ‘0’ may indicate that the field does not include amost recently repeated value (e.g., it has been encountered for thefirst time in the record during a top-down scan). In various examples, a‘required’ field in a path does not have a repetition level. Forexample, if the ‘Resolution’ field is required for the‘Video.Resolution.Width’ path, the range of resolution levels may beeither ‘0’ or ‘1.’ ‘Resolution’ may not have a level because it isalways present in the record when the ‘Video’ field is present. Thus, if‘Resolution’ was assigned a level of ‘2,’ it may always be encounteredbefore ‘Video’ and thus a level of ‘1’ may not ever be assigned. Thus,not including a repetition level for required fields may enable a numberof different resolution levels to be reduced, and a number of bits torepresent the resolution level may be reduced.

If the field ‘Width’ in the above example is an ‘optional’ or‘repeating’ field, a record may not always include a value for the‘Width’ field. Thus, a column of values for the ‘Video.Resolution.Width’path may use a mechanism to designate when a ‘Video’ or a‘Video.Resolution’ path is found in the record but the ‘Width’ field hasnot been instantiated in the record. This mechanism may include storing,in the ‘Video.Resolution.Width’ column of data, a ‘Definition’ level foreach ‘Video’ or ‘Video.Resolution’ field in the record regardlesswhether the ‘Width’ field is instantiated. The ‘Definition’ level mayindicate how many of the fields in the ‘Video.Resolution.Width’ paththat could be missing (e.g., because the field is optional orrepeatable) are actually present.

Thus, if the field ‘Video’ is present in the record but no corresponding‘Resolution’ child is instantiated, a definition level of ‘1’ may berecorded in the ‘Video.Resolution.Width’ column. If the field‘Video.Resolution’ is present in the record, but no corresponding‘Width’ child is instantiated, a definition level of ‘2’ may berecorded. If the field ‘Video.Resolution.Width’ is present in therecord, a definition level of ‘3’ may be recorded.

Therefore, whenever the ‘Definition’ level (which represents the numberof fields that could be undefined but are actually defined) is less thanthe number of fields that could be defined, a missing occurrence of the‘Width’ field may be identified. The combination of the ‘Repetition’level and the ‘Definition’ level may enable the structure of the recordto be reconstructed.

A column of data for a particular field (e.g., the‘Video.Resolution.Width’ field) may include the values for the fieldfrom multiple records, corresponding repetition and definition levels(acknowledging that some ‘missing’ values may have a repetition and adefinition level), and header information. In some examples, the valuesare stored consecutively and adjacent. In other words, if a value forone ‘Video.Resolution.Width’ field was ‘700’ and the value for a next‘Video.Resolution.Width’ field was ‘800,’ a portion of the column asstored in memory may read ‘700800,’ In this example, a header in thecolumn may identify that the each value has a fixed width (e.g., a fixedbinary representation to hold the numbers 700 and 800).

In some examples, the stored values are represented by strings. Forexample, instances of the ‘Width’ field may include the values ‘Small’and ‘Medium.’ In some examples, the various string values may be a fixedlength (e.g., a null value may be added to the beginning or end of the‘Small’ value to make the string the same length as the ‘Medium’ value).In some examples, however, each stored string may include an identifierin a beginning portion of the string that identifies a length of thestring. For example, the ‘small’ value may include an identifier thatindicates that the string is five digits long (or a corresponding numberof binary bits).

Because the values may be stored consecutively in the columnar stripe,the ‘repetition’ and ‘definition’ levels may be stored at the beginningof the columnar stripe. In some examples, the ‘repetition’ and‘definition’ levels are stored in pairs for a particular value (whetherinstantiated or missing). As an illustration, a repetition level of 3may be stored in the first four bits of a byte and a definition level of1 may be stored in the last four bits of the byte. A next byte in theheader may include a repetition level and a definition level for thenext instance of the field in the record (or the first instance in thesubsequent record).

The number of bits used to represent the repetition and definitionlevels may be based on a maximum level value. For example, if themaximum repetition level is 3, the repetition level may be representedwith two bits. If the maximum repetition level is 4 the repetition levelmay be represented with three bits. The header may include informationthat identifies the length of the repetition and definition levels.

In various examples, the repetition levels may be stored consecutivelyin memory and the definition levels may be stored consecutively inmemory (e.g., not in pairs). In various examples, the repetition anddefinition levels may be stored in a group with their correspondingvalue (if the value is instantiated). In other words, a sequence ofinformation in the columnar stripe may readValue1:RepetitionLevel1:DefinitionLevel1:Value2:RepetitionLevel2:DefinitionLevel2,and so on.

The columnar stripes may be compressed into blocks of information. Forexample, each columnar stripe may be split into a set of blocks, witheach block including its own respective header. A first block mayinclude the first 800,000 values and a second block may include a second800,000 values from a stripe of 1.6 million values. A block header mayinclude the repetition and definition levels along with additionalinformation that may be used to help analyze the portion of the columnarstripe that is represented by the block, and to reconstruct the columnarstripe.

In some examples, the block header includes an ‘Assertion’ value thatdefines a type of data that is found in the block's values. For example,a block for the ‘Video.Resolution.Width’ field may not include anyvalues that list ‘Large’ width resolution. Thus, the ‘Assertion’ valuemay indicate that the values only include ‘Small’ and ‘Medium’ values.If a query is performed for records that include ‘High’ width resolutionvideos, then the described block may be avoided by the querying system.

The system described in this document may perform queries on columnarstripes without reconstructing the information in the columnar stripesinto records, and without loading information from the columnar stripesinto a database (e.g., without using ‘Insert’ clause). Thus, the datamay be accessed in situ, which may provide computational analysis timesavings on the order of magnitudes.

The querying system may employ many of the clauses employed for queryingrelational databases. Additional clauses that are specific tonon-relational data, however, may be employed. For example, a WITHINclause may allow for operations to be performed on multiple instances ofa field within a single record or a portion of a record. A relationaldatabase, however, may be unable to store more than a single instance ofa field in a row (e.g., a representation of a record). Thus, a query ona relational database may be fundamentally unable to perform queries‘within’ a record.

As an example of the WITHIN clause, values for a particular field may bemultiplied. Supposing that the query instructions request that allvalues for ‘MutualFund.InterestRate’ be multiplied together for aparticular record (where each record may be for a particular accountholder). The querying system may find all of the‘MutualFund.InterestRate’ values within the single record and multiplythem together.

Another example of a clause that may be specific to non-relationalnested data is the OMIT IF clause. This clause may enable a record to befiltered to remove instances of fields if a particular condition is met(e.g., a new columnar stripe or record may be created with specifiedfields removed). As an illustration, a stripe of values that listemployee salaries may be queried and a new stripe that removesemployee's with salaries above $90,000 may be generated using the OMITIF clause.

The querying system may be hosted by a server system and provided overthe internet to remote computing devices through application programminginterfaces (API). In general, the columnar data may be represented toexternal users of the remote computing devices as stored within tablesof information. The users may generate the tables using API calls andmay fill the tables with data from a repository of objects.

The users may use separate API calls to load objects into therepository. For example, the server system may also implement aninternet-accessible storage system that enables users to push data tothe server system for remote hosting. In this manner, the data storageservice may serve as a repository for data aggregated from manygeographically dispersed computing devices. For example, internetwebsite logs may be streamed by hundreds of computers to the storagesystem and be stored as individual objects in one or more “buckets” atthe repository. A given bucket may have an access control list thatdetermines which computing devices or user accounts are authorized toupload objects to the bucket or to access objects in a bucket.Similarly, individual objects may have associated access control liststhat control which devices or user accounts are able to access ormanipulate the object.

A user may explicitly request that the data in objects in a bucket betransferred to a table, or may establish a service that monitors thebucket and transfers the data in newly placed objects into the table. Insome implementations, the transfer of data in the objects to the tablemay include converting the data format of the objects to a differentformat, generating columnar stripes for the data in the records, andplacing the columnar stripes in a different repository. Metadata for thetable may be updated to reference the columnar stripes that include theconverted data for the imported objects.

Thus, in some implementations, when the querying service receives arequest to query a table, the metadata for the table is located and aquery is performed on the columnar data that underlies the table. Theoutput of the query may be placed in a different table, provided to theremote device requesting the query, or may be stored in the repositoryof objects as an object (e.g., an object that includes a collection ofrecords).

1. Introduction

Large-scale parallel computing may be performed using shared clusters ofcommodity machines. See L. A. Barroso and U. Holzle. The Datacenter as aComputer: An Introduction to the Design of Warehouse-Scale Machines.Morgan & Claypool Publishers, 2009. A cluster may host a multitude ofdistributed applications that share resources, have widely varyingworkloads, and run on machines with different hardware parameters. Anindividual computing machine in a distributed application may take muchlonger to execute a given task than others, or may never complete due tofailures or preemption by a cluster management system. Hence, dealingwith stragglers (e.g., computing tasks with significant latency) andfailures may achieve fast execution and fault tolerance. See G.Czajkowski. Sorting 1PB with MapReduce. Official Google Blog, Nov. 2008.Athttp://googleblog.blogspot.com/2008/11/sorting-1pb-with-mapreduce.html.

The data used in web and scientific computing is often nonrelational.Hence, a flexible data model may be beneficial in these domains. Datastructures used in programming languages, messages exchanged bydistributed systems, web traffic logs, etc. may lend themselves to anested representation. For example, a nested representation of data mayinclude a multiple fields that each include several levels of childrenfields. Some of the children fields may include corresponding data.Normalizing and recombining such data at web scale may becomputationally expensive. A nested data model underlies some of thestructured data processing at major web companies.

This document describes a system that supports interactive analysis ofvery large datasets over shared clusters of commodity machines. Unliketraditional databases, it is capable of operating on in situ nesteddata. In situ refers to the ability to access data ‘in place’, forexample, in a distributed file system like Google File System (see S.Ghemawat, H. Gobioff, and S.-T. Leung. The Google File System. In SOSP,2003) or another storage layer like Bigtable (see F. Chang, J. Dean, S.Ghemawat, W. C. Hsieh, D. A. Wallach, M. Burrows, T. Chandra, A. Fikes,and R. Gruber. Bigtable: A Distributed Storage System for StructuredData. In OSDI, 2006).

The system can execute many queries over such data that may ordinarilyrequire a sequence of MapReduce jobs, but at a fraction of the executiontime. See J. Dean and S. Ghemawat. MapReduce: Simplified Data Processingon Large Clusters. In OSDI, 2004. The described system may be used inconjunction with MapReduce to analyze outputs of MapReduce pipelines orrapidly prototype larger computations. Examples of using the systeminclude:

-   -   Analysis of web logs and crawled web documents;    -   Install data for applications served by an online marketplace;    -   Crash data for application products;    -   Multimedia playback statistics;    -   OCR results from scans of books;    -   Spam analysis;    -   Debugging of map tiles;    -   Tablet migrations in managed Bigtable instances;    -   Results of tests run on a distributed build system;    -   Disk I/O statistics for hundreds of thousands of disks;    -   Execution logs of MapReduce jobs across several data centers;        and    -   Symbols and dependencies in a codebase.

The described system builds on ideas from web search and paralleldatabase management systems. First, its architecture builds on theconcept of a serving tree used in distributed search engines. See J.Dean. Challenges in Building Large-Scale Information Retrieval Systems:Invited Talk. In WSDM, 2009. Like a web search request, a query getspushed down the tree and rewritten at each step. The result of the queryis assembled by aggregating the replies received from lower levels ofthe tree.

Second, the described system provides a high-level, SQL-like language toexpress ad hoc queries. In contrast to layers such as Pig (see C.Olston, B. Reed, U. Srivastava, R. Kumar, and A. Tomkins. Pig Latin: aNot-so-Foreign Language for Data Processing. In SIGMOD, 2008.) and Hive(Hive. http://wiki.apache.org/hadoop/Hive, 2009), the querying systemexecutes queries natively without translating them into MapReduce jobs.

Lastly, the described system uses a column-striped storagerepresentation, which enables it to read less data from secondarystorage and reduce CPU cost due to cheaper compression. Column storesfor analyzing relational data, (D. J. Abadi, P. A. Boncz, and S.Harizopoulos. Column-Oriented Database Systems. VLDB, 2(2), 2009), arenot believed to have extended to nested data models. The columnarstorage format that is described may be supported by MapReduce, Sawzall(see R. Pike, S. Dorward, R. Griesemer, and S. Quinlan. Interpreting theData: Parallel Analysis with Sawzall. Scientific Programming, 13(4),2005), and FlumeJava (see C. Chambers, A. Raniwala, F. Perry, S. Adams,R. Henry, R. Bradshaw, and N. Weizenbaum. FlumeJava: Easy, EfficientData-Parallel Pipelines. In PLDI, 2010).

In Section 4, this document describes a columnar storage format fornested data. Algorithms are presented for dissecting nested records intocolumns and reassembling them.

In Section 5, a query language for processing data in that is stored inthe columnar storage format is described. The query language andexecution of the language are designed to operate efficiently oncolumn-striped nested data and do not require restructuring of nestedrecords.

In Section 6, an illustration of applying execution trees that are usedin web search serving systems to database processing is provided. Thebenefits for answering aggregation queries efficiently is explained.

In Section 7, experiments conducted on system instances are presented.

Section 2 Example Scenario

Suppose that Alice, an engineer at a web-search company, comes up withan idea for extracting new kinds of signals from web pages. She runs aMapReduce job that cranks through the input data that includes contentfrom the web pages and produces a dataset containing the new signals,stored in billions of records in a distributed file system. To analyzethe results of her experiment, she launches the system described in thisdocument and executes several interactive commands:

  DEFINE TABLE t AS /path/to/data/* SELECT TOP(signal1, 100), COUNT(*)FROM t

Alice's commands execute in seconds. She runs a few other queries toconvince herself that her algorithm works. She finds an irregularity insignall and digs deeper by writing a FlumeJava program that performs amore complex analytical computation over her output dataset. Once theissue is fixed, she sets up a pipeline which processes the incominginput data continuously. She formulates a few canned SQL queries thataggregate the results of her pipeline across various dimensions, andadds them to an interactive dashboard (e.g., a web page about a servicethat explains the service and details statistics on the service).Finally, she registers her new dataset in a catalog so other engineerscan locate and query the dataset quickly.

The above scenario may require interoperation between the queryprocessor and other data management tools. The first ingredient for suchinteroperation is a common storage layer. The Google File System is onesuch distributed storage layer that may be used. The Google File Systemmanages very large replicated datasets across thousands of machines andtens of thousands of disks.

Replication helps preserve the data despite faulty hardware and achievefast response times in presence of stragglers. A high-performance sharedstorage layer is a key enabling factor for in situ data management. Itallows accessing the data without a time-consuming loading phase, whichis a major impedance to database usage in analytical data processing(where it is often possible to run dozens of MapReduce analyses before adatabase management system is able to load the data and execute a singlequery). For example, when a database management system is used toanalyze data, the database may need to be loaded with data using‘Insert’ commands. Such loading may not be required by the describedsystem. As an added benefit, data in a file system can be convenientlymanipulated using standard tools, e.g., to transfer to another cluster,change access privileges, or identify a subset of data for analysisbased on file names.

A second ingredient for building interoperable data managementcomponents is a shared storage format. Columnar storage is used for flatrelational data but adapting columnar storage to a nested data modelallows the technique to be applied to web data. FIG. 1 illustrates theidea that all values of a nested field in a data structure are storedcontiguously. For example, in the column-oriented representation ofnested data, all values for a particular nested field within a datastructure (e.g., the field A.B.C) are stored adjacent to each other andcontiguously in memory. Hence, values for the field A.B.C can beretrieved from memory without reading values from the field A.E andvalues from the field A.B.D.

Additionally, values for the same particular field in differentinstances of a data structure (e.g., a ‘record’) may be storedcontiguously. For example, the values for field A.B.C for the record‘r1’ are stored adjacent to the values for the same field for the record‘r2.’ To the contrary, in the ‘record-oriented’ representation of nesteddata, values for all fields within a particular record are storedcontiguously. In other words, the data values for a particular field arenot bunched together.

The challenge that the described columnar storage format addresses ishow to preserve all structural information and be able to reconstructrecords from an arbitrary subset of fields. This document next discussesthe data model from which the fields in the columnar storage format maybe filled, and then turn to algorithms for processing the columnarstorage and query processing on data in the columnar storage.

Section 3 Data Model

This section describes the data model used by the described system andintroduces some terminology used later. The described Protocol Buffersdata model originated in the context of distributed systems, and isavailable as an open source implementation. See (Protocol Buffers:Developer Guide. Available athttp://code.google.com/apis/protocolbuffers/docs/overview.html). Thedata model is based on strongly-typed nested records. Its abstractsyntax is given by:

Σ=dom|<A ₁ :τ[*|?], . . . ,A _(n):τ[*|?]>

where τ is an atomic type or a record type. Atomic types in dom compriseintegers, floating-point numbers, strings, etc. Records consist of oneor multiple fields. Field i in a record has a name A_(i) and an optionalmultiplicity label. Repeated fields (*) may occur multiple times in arecord. They are interpreted as lists of values, i.e., the order offield occurrences in a record is significant. Optional fields (?) may bemissing from the record. Otherwise, a field is required (e.g., mustappear exactly once).

As an illustration, FIG. 2 depicts a schema that defines a record type‘Document,’ which represents a web document. The schema definition usesthe Protocol Buffers syntax. A ‘Document’ has a required integer ‘DocId’and optional ‘Links,’ containing a list of ‘Forward’ and ‘Backward’entries holding ‘DocIds’ of other web pages. A ‘Document’ can havemultiple ‘Names,’ which are different URLs by which the document can bereferenced. A ‘Name’ contains a sequence of ‘Code’ and (optional)‘Country’ pairs. FIG. 2 also shows two sample records, r1 and r2, thatconform to the schema. The record structure is outlined usingindentation. The sample records r1 and R2 in FIG. 2 are used explain thealgorithms throughout this document. The fields defined in the schemaform a tree hierarchy. The full path of a nested field is denoted usinga dotted notation, e.g., Name.Language.Code is the full path name forthe ‘Code’ field depicted in FIG. 2.

The nested data model backs a platform-neutral, extensible mechanism forserializing structured data. Code generation tools produce bindings fordifferent programming languages such as C++ or Java. Cross-languageinteroperability is achieved using a standard binary on-the-wirerepresentation of records, in which field values are laid outsequentially as they occur in the record. This way, a MapReduce programwritten in Java can consume records from a data source exposed via a C++library. Thus, if records are stored in a columnar representation,assembling them fast may assist interoperation with MapReduce and otherdata processing tools.

Section 4 Nested Columnar Storage

As illustrated in FIG. 1, a goal is to store all values of a given fieldconsecutively to improve retrieval efficiency. In this section, thechallenges of lossless representation of record structure in a columnarformat (Section 4.1), fast encoding (Section 4.2), and efficient recordassembly (Section 4.3) are addressed.

Section 4.1 Repetition and Definition Levels

A consecutive list of values alone do not convey the structure of arecord. Given two values of a field that is repeated in a record, asystem may not be able to determine at what ‘level’ the value isrepeated (e.g., whether the two values are from different records or arefrom the same record). Likewise, if an optional field is missing from arecord, values alone may not convey which enclosing records were definedexplicitly and which were not. The concepts of repetition and definitionlevels are thus introduced. FIG. 3 includes tables that summarize therepetition and definition levels for atomic fields in the sample recordsthat are depicted in FIG. 1.

Repetition Levels.

Consider the field ‘Code’ in FIG. 2. It occurs three times in record‘r1.’ Occurrences ‘en-us’ and ‘en’ are inside the first ‘Name’ field,while ‘en-gb’ is in the third ‘Name’ field. To disambiguate theseoccurrences in the columnar structure, a repetition level is attached toeach value that is to be stored in the columnar structure. Therepetition level indicates at what repeated field in the field's paththe value has repeated. For example, the field path Name.Language.Codecontains two fields that are repeated, ‘Name’ and ‘Language.’ Hence, therepetition level of Code ranges between 0 and 2. Level 0 denotes thestart of a new record, level 1 denotes a recent repetition at the ‘Name’field, and level 2 denotes a recent repetition at the ‘Language’ field.

As an illustration of determining the level for a field, record ‘r1’ maybe scanned from the top down. The value ‘en-us’ is first encountered anda check may be performed to identify the field in the Name.Language.Codepath that has most recently repeated in the record. In this example,none of the fields have been repeated and thus, the repetition level is0. The value ‘en’ is next encountered for the Name.Language.Code pathand the field ‘Language’ is identified as the field that has mostrecently repeated. For example, scanning upwards from the value ‘en,’the first field in the Name.Language.Code path that repeats is‘Language.’ Thus, the repetition level is 2 (e.g., because ‘2’corresponds to the ‘Language’ field because ‘Language’ is the secondfield in the Name.Language.Code path that repeats). Finally, when thevalue ‘en-gb’ is encountered, the field ‘Name’ has repeated mostrecently (the ‘Language’ field occurred only once after Name), so therepetition level is 1. In other words, the repetition level for a valuemay be a number that represents a most recently repeated field. Thus,the repetition levels of Code values in record ‘r1’ are 0, 2, 1.

Notice that the second ‘Name’ field in record ‘r1’ does not contain anyvalues for the field ‘Code.’ To determine that ‘en-gb’ occurs as a valuefor a field nested within the third instance of the field ‘Name,’ andnot in the second instance, a NULL value is added between the values‘en’ and ‘en-gb’ as they are stored in the columnar structure (see FIG.3). ‘Code’ is a required child field of the ‘Language’ field, so thefact that a value for the ‘Code’ field is missing implies that the‘Language’ field is not also not defined. In general though, determiningthe level up to which nested records exist may require additionalinformation.

Definition Levels.

Each value of a field with path ‘p,’ especially every NULL value, has a‘definition level’ that specifies how many fields in the path ‘p’ thatcould be undefined (e.g., because the fields are optional or repeated)are actually present in the record. To illustrate, observe that record‘r1’ has no ‘Backward’ fields for the ‘Links’ field. Still, the field‘Links’ is defined (at a level of 1). To preserve this information, aNULL value with definition level of 1 is added to the ‘Links.Backward’column.

In other words, specifying a level of 1 for the ‘Links.Backward’ pathindicates that ‘1’ field that was optional or repeated (i.e., the‘Links’ field) was defined in a path that includes two fields that areoptional or repeated (i.e., the ‘Links’ field and the ‘Backward’ field).Thus, a definition of ‘1’ indicates that the ‘Backward’ field was notinstantiated. Similarly, the missing occurrence of‘Name.Language.Country’ in record ‘r2’ carries a definition level 1,while its missing occurrences in record ‘r1’ have definition levels of 2(inside ‘Name.Language’) and 1 (inside ‘Name’), respectively. Theencoding procedure outlined above may preserve the record structurelosslessly.

Encoding.

As stored in memory, each column that corresponds to a particular fieldmay be stored with a header that includes a contiguous listing ofrepetition and definition values, followed by a contiguous listing ofthe substantive values. Each repletion and definition value may bestored as bit sequences (e.g., in a single byte). For example, the firstfour bits of a byte may be used to represent the repetition level for aparticular value and the last four bits may be used to represent thedefinition level. In some examples, the header may include definitionsof lengths of the number of bits so that delimiters may not be used.Thus, bits may only be used as necessary. For example, if the maximumdefinition level is 3, two bits per definition level may be used.

Thus, a representation of columnar data for a single field (e.g., the‘Name.Language.Code’ field) may be stored in memory with a sequence ofbytes representing the repetition and definition levels for acorresponding sequence of values, followed by a sequence of values. NULLvalues, however, may not be stored explicitly as they may be determinedby analyzing the definition levels. For instance, any definition levelthat is smaller than the number of repeated and optional fields in afield's path can denote a NULL. Thus, a system may be able to determinewhere in the listing of consecutive values a NULL value should beinserted or inferred. In some examples, definition levels are not storedfor values that are always defined. Similarly, repetition levels mayonly be stored if required. For example, a definition level of 0 impliesa repetition level of 0, so the latter may be omitted. In fact,referencing the structures illustrated in FIG. 3, no levels may storedfor the ‘DocId’ field.

A representation of columnar data in memory may be broken up into a setof blocks. Each block may include a header that includes the repetitionand definition level information, and a subsequent listing of the valuesfor the field. Each header may include a ‘constraint’ value thatindicates an allowable range of values in the block. Thus, the describedsystem may identify which blocks include data that the system isinterested in. The constraint can also indicate other properties of thevalues, e.g., whether the values have been sorted. In general, the‘constraint’ may be thought of as an ‘assertion’ about what kind ofvalues are found in the block. Each block may be compressed.

Section 4.2 Splitting Records into Columns

The above description presented an encoding of the record structure in acolumnar format. A challenge is how to produce column stripes withrepetition and definition levels efficiently. The base algorithm forcomputing repetition and definition levels is provided below. Thealgorithm recurses into the record structure and computes the levels foreach field value. As illustrated earlier, repetition and definitionlevels may need to be computed even if field values are missing. Manydatasets are sparse and it may not be uncommon to have a schema withthousands of fields, only a hundred of which are used in a given record.Hence, it may be beneficial to process missing fields as cheaply aspossible. To produce column stripes, a tree of field writers is created,whose structure matches the field hierarchy in the schema. The basicidea is to update field writers only when they have their own data, andnot try to propagate parent state down the tree unless absolutelynecessary. To do that, child writers inherit the levels from theirparents. A child writer synchronizes to its parent's levels whenever anew value is added.

An example algorithm for decomposing a record into columns is shown inFIG. 4. Procedure ‘DissectRecord’ is passed an instance of a‘RecordDecoder,’ which is used to traverse binary-encoded records.‘FieldWriters’ form a tree hierarchy isomorphic to that of the inputschema. The root ‘FieldWriter’ is passed to the algorithm for each newrecord, with ‘repetitionLevel’ set to 0. The primary job of the‘DissectRecord’ procedure is to maintain the current ‘repetitionLevel.’The current ‘definitionLevel’ is uniquely determined by the treeposition of the current writer, as the sum of the number of optional andrepeated fields in the field's path.

The while-loop of the algorithm (Line 5) iterates over all atomic andrecord-valued fields contained in a given record. The set ‘seen Fields’tracks whether or not a field has been seen in the record. It is used todetermine what field has repeated most recently. The child repetitionlevel ‘chRepetitionLevel’ is set to that of the most recently repeatedfield or else defaults to its parent's level (Lines 9-13). The procedureis invoked recursively on nested records (Line 18).

The document above referenced ‘FieldWriters’ accumulating levels andpropagating them lazily to lower-level writers. This may be performed byeach non-leaf writer keeping a sequence of (repetition, definition)levels. Each writer also has a ‘version’ number associated with it.Simply stated, a writer version is incremented by one whenever a levelis added. It is sufficient for children to remember the last parent'sversion they synced. If a child writer ever gets its own (non-null)value, it synchronizes its state with the parent by fetching new levels,and only then adds the new data.

Because input data may have thousands of fields and millions records, itmay not be feasible to store all levels in memory. Some levels may betemporarily stored in a file on disk. For a lossless encoding of empty(sub)records, non-atomic fields (such as Name.Language in FIG. 2) mayneed to have column stripes of their own, containing only levels but nonon-NULL values.

Section 4.3 Record Assembly

Assembling records (e.g., records ‘r1’ and ‘r2’) from columnar dataefficiently is critical for record-oriented data processing tools (e.g.,Map Reduce). Given a subset of fields, a goal is to reconstruct theoriginal records as if they contained just the selected fields, with allother fields stripped away. The key idea is to create a finite statemachine (FSM) that reads the field values and levels for each field, andappends the values sequentially to the output records. An FSM statecorresponds to a field reader for each selected field. State transitionsare labeled with repetition levels. Once a reader fetches a value, thenext repetition level is looked at to decide what next reader to use.The FSM is traversed from the start to end state once for each record.

FIG. 5 shows an FSM that reconstructs the complete records in ourrunning example using as input the blocks described in Section 4.1. Inthis example, the nodes are labeled with fields and the edges arelabeled with repetition levels. The start state is ‘DocId.’ Once a‘DocId’ value is read, the FSM transitions to the ‘Links.Backward’state. After all repeated ‘Backward’ values have been drained, the FSMjumps to ‘Links.Forward,’ etc.

To sketch how FSM transitions are constructed, let ‘l’ be the nextrepetition level returned by the current field reader for field ‘f.’Starting at ‘f’ in the schema tree (e.g., the schema in FIG. 2), itsancestor is found that repeats at level ‘l’ and select the first leaffield ‘n’ inside that ancestor. This provides an FSM transition (‘f’;‘l’)→n. For example, let ‘l’=1 be the next repetition level read by‘f’=‘Name.Language.Country.’ Its ancestor with repetition level ‘1’ isName, whose first leaf field is ‘n’=‘Name.Url.’

If only a subset of fields need to be retrieved, a simpler FSM that ischeaper to execute may be constructed. FIG. 6 depicts an FSM for readingthe fields ‘DocId’ and ‘Name.Language.Country.’ The figure shows theoutput records ‘s1’ and ‘s2’ produced by the automaton. Notice that theencoding and the assembly algorithm preserve the enclosing structure ofthe field ‘Country.’ This may be important for applications that need toaccess, e.g., the Country appearing in the first Language of the secondName. In XPath, this may correspond to the ability to evaluateexpressions like /Name[2]/Language[1]/Country.

Construct FSM Procedure.

FIG. 7 shows an algorithm for constructing a finite-state machine thatperforms record assembly. The algorithm takes as input the fields thatshould be populated in the records, in the order in which they appear inthe schema. The algorithm uses a concept of a ‘common repetition level’of two fields, which is the repetition level of their lowest commonancestor. For example, the common repetition level of ‘Links.Backward’and ‘Links.Forward’ equals 1. The second concept is that of a ‘barrier’,which is the next field in the sequence after the current one. Theintuition is that each field is attempted to be processed one by oneuntil the barrier is hit and requires a jump to a previously seen field.

The algorithm consists of three steps. In Step 1 (Lines 6-10), thecommon repetition levels are processed backwards. These are guaranteedto be non-increasing. For each repetition level encountered, theleft-most field in the sequence is picked—that is the field that is tobe transitioned to when that repetition level is returned by a‘FieldReader.’ In Step 2, the gaps are filled (Lines 11-14). The gapsarise because not all repetition levels are present in the commonrepetition levels computed at Line 8. In Step 3 (Lines 15-17),transitions for all levels are set that are equal to or below thebarrier level to jump to the barrier field. If a ‘FieldReader’ producessuch a level, the nested record may continue to be constructed and theremay be no need to bounce off the barrier.

Assemble Record Procedure.

An Assemble Record procedure (illustrated in FIG. 8) takes as input aset of ‘FieldReaders’ and (implicitly) the FSM with state transitionsbetween the readers. In other words, the algorithm operates on an FSMand columnar data and outputs constructed records. Variable reader holdsthe current ‘FieldReader’ in the main routine (Line 4). Variable Readerholds the last reader whose value is appended to the record and isavailable to all three procedures shown in FIG. 7. The main while-loopis at Line 5. The next value is fetched from the current reader. If thevalue is not NULL, which is determined by looking at its definitionlevel, the record being assembled is synchronized to the recordstructure of the current reader in the method ‘MoveToLevel,’ and thefield value is appended to the record. Otherwise, the record structuremay be adjusted without appending any value—which may be done if emptyrecords are present. On Line 12, a ‘full definition level’ is used.Recall that the definition level factors out required fields (onlyrepeated and optional fields are counted). Full definition level takesall fields into account.

Procedure ‘MoveToLevel’ transitions the record from the state of the‘lastReader’ to that of the ‘nextReader’ (see Line 22). For example,suppose the ‘lastReader’ corresponds to ‘Links. Backward’ in FIG. 2 and‘nextReader’ is ‘Name.Language.Code.’ The method ends the nested recordLinks and starts new records Name and Language, in that order. Procedure‘ReturnsToLevel’ (Line 30) is a counterpart of ‘MoveToLevel’ that onlyends current records without starting any new ones.

In their on-the-wire representation, records are laid out as pairs of afield identifier followed by a field value. Nested records can bethought of as having an ‘opening tag’ and a ‘closing tag’, similar toXML (actual binary encoding may differ). A description of ‘starting’ arecord refers to writing opening tags, while ‘ending’ a record refers towriting closing tags.

Section 5 Query Language

The described system may employ a query language that is based on SQLand is designed to be efficiently implementable on columnar nestedstorage. Aspects of the query language are described herein. EachSQL-like statement (and algebraic operators it translates to) takes asinput one or multiple nested tables (e.g., a set of compressed blocks ofcolumnar data that represents a table, as described in Section 4.1) andtheir schemas, and produces a nested table (e.g., a modified instance ofthe columnar data) and its output schema. FIG. 9 depicts a sample querythat performs projection, selection, and within-record aggregation. Thequery is evaluated over the table t={r1, r2} from FIG. 2. The fields arereferenced using path expressions. The query produces a nested resultalthough no record constructors are present in the query.

To explain what the query does, consider the selection operation (theWHERE clause). Think of a nested record as a labeled tree, where eachlabel corresponds to a field name. The selection operator prunes awaythe branches of the tree that do not satisfy the specified conditions.Thus, only those nested records are retained where ‘Name.Url’ is definedand starts with ‘http.’ Next, consider projection. Each scalarexpression in the SELECT clause emits a value at the same level ofnesting as the most-repeated input field used in that expression. So,the string concatenation expression emits ‘Str’ values at the level of‘Name.Language.Code’ in the input schema.

The COUNT expression illustrates within-record aggregation. Theaggregation is done WITHIN each ‘Name’ subrecord, and emits the numberof occurrences of ‘Name.Language.Code’ for each ‘Name’ as a non-negative64-bit integer (uint64). Thus, the WITHIN statement enables intra-rowaggregation. In other words, records of the same name may be aggregatedin a same record or beneath a same child. In contrast, SQL, which maynot be able to operate on nested data, may be unable to operate onintra-row records.

The language supports nested subqueries, inter and intra-recordaggregation, top-k, joins, user-defined functions, etc. Some of thesefeatures are discussed in the experimental data section. As oneadditional example, the described query language includes an OMIT IFstatement that can filter an intra-row group of values. For example,each of thousands of records may include several repeated ‘Cost’ fieldsthat each include a numerical value. An user of the query language maywant to throw out all records where a sum of the values in the fieldsexceeds the number ‘20.’ Thus, the user may employ an OMIT IF statementto generate a list of the records where the summed ‘Cost’ in each recordis twenty or less.

Section 6 Query Execution

Tree Architecture.

The described system uses a multi-level serving tree to execute queries(see FIG. 10). A root server receives incoming queries, reads metadatafrom the tables, and routes the queries to the next level in the servingtree. The leaf servers communicate with the storage layer or access thedata on local disk. Many of the queries that operate in the describedsystem are single-scan aggregations; therefore, this document focuses onexplaining those and uses them for experiments in the next section.Consider a simple aggregation query below:

-   -   SELECT A, COUNT(B) FROM T GROUP BY A

When the root server receives the above query, it determines alltablets, i.e., horizontal partitions of the table, that comprise thetable ‘T’ and rewrites the query as follows:

-   -   SELECT A, SUM(c) FROM (R₁ ¹ UNION ALL . . . R_(n) ¹) GROUP BY A

Tables R₁ ¹ UNION ALL . . . R_(n) ¹ are the results of queries sent tothe nodes 1, . . . , n at level 1 of the serving tree:

-   -   R_(i) ¹=SELECT A, COUNT(B) AS c FROM T_(i) ¹ GROUP BY A

T_(i) ¹ is a disjoint partition of tablets in ‘T’ processed by server‘i’ at level ‘1.’ Each serving level performs a similar rewriting.Ultimately, the queries reach the leaves, which scan the tablets in ‘T’in parallel. On the way up, intermediate servers perform a parallelaggregation of partial results. The execution model presented above iswell suited for aggregation queries returning small and medium-sizedresults, which are a very common class of interactive queries.

Query Dispatcher.

The described system is a multi-user system, e.g., several queries maybe executed simultaneously. A query dispatcher schedules queries basedon their priorities and balances the load. Another role is to providefault tolerance when one server becomes much slower than others or atablet replica becomes unreachable.

The amount of data processed in each query is often larger than thenumber of processing units available for execution, which are calledslots. A slot corresponds to an execution thread on a leaf server. Forexample, a system of 3,000 leaf servers each using 8 threads has 24,000slots. So, a table spanning 100,000 tablets can be processed byassigning about 5 tablets to each slot. During query execution, thequery dispatcher computes a histogram of tablet processing times. If atablet takes a disproportionately long time to process, the systemreschedules the tablet on another server. Some tablets may need to beredispatched multiple times.

The leaf servers read stripes of nested data in columnar representation.The blocks in each stripe are prefetched asynchronously; the read-aheadcache typically achieves hit rates of 95%. Tablets are usually three-wayreplicated. When a leaf server cannot access one tablet replica, itfalls over to another replica.

The query dispatcher honors a parameter that specifies the minimumpercentage of tablets that must be scanned before returning a result. Asdescribed below, setting such parameter to a lower value (e.g., 98%instead of 100%) can often speed up execution significantly, especiallywhen using smaller replication factors.

Each server may have an internal execution tree, as depicted on theright-hand side of FIG. 7. The internal tree corresponds to a physicalquery execution plan, including evaluation of scalar expressions.Optimized, type-specific code is generated for most scalar functions. Abasic execution plan consists of a set of iterators that scan inputcolumns in lockstep and emit results of aggregates and scalar functionsannotated with the correct repetition and definition levels, bypassingrecord assembly entirely during query execution.

Some queries by the described system, such as top-k and count-distinct,return approximate results using well-known single-scan algorithms. SeeHailing Yu, Hua-gang Li, Ping Wu, Divyakant Agrawal, Amr El Abbadi,“Efficient processing of distributed top-k queries”, DEXA 2005, pp.65-74.

Section 7 Experimental Data

This section presents an experimental evaluation of the described systemon several datasets, and examines the effectiveness of columnar storagefor nested data. The properties of the datasets used in the study aresummarized in FIG. 11. In uncompressed, non-replicated form the datasetsoccupy about a petabyte of space. All tables are three-way replicated,except one two-way replicated table, and contain from 100K to 800Ktablets of varying sizes. This section begins by examining the basicdata access characteristics on a single machine, then show how columnarstorage benefits MapReduce execution, and finally focus on the describedsystem's performance. The experiments were conducted on system instancesrunning in two data centers next to many other applications, duringregular business operation. Table and field names used below areanonymized.

Local Disk. In the first experiment, performance tradeoffs of columnarvs. record-oriented storage were examined by scanning a 1 GB fragment oftable T1 containing about 300K rows (see FIG. 12). The data is stored ona local disk and takes about 375 MB in compressed columnarrepresentation. The record-oriented format uses heavier compression yetyields about the same size on disk. The experiment was done on adual-core Intel machine with a disk providing 70 MB/s read bandwidth.All reported times are cold; OS cache was flushed prior to each scan.

FIG. 12 shows five graphs, illustrating the time it takes to read anduncompress the data, and assemble and parse the records, for a subset ofthe fields. Graphs (a)-(c) outline the results for columnar storage.Each data point in these graphs was obtained by averaging themeasurements over 30 runs, in each of which a set of columns of a givencardinality was chosen at random. Graph (a) shows reading anddecompression time. Graph (b) adds the time needed to assemble nestedrecords from the columns. Graph (c) shows how long it takes to parse therecords into strongly typed C++ data structures.

Graphs (d)-(e) depict the time for accessing the data on record-orientedstorage. Graph (d) shows reading and decompression time. A bulk of thetime is spent in decompression; in fact, the compressed data can be readfrom the disk in about half the time. As Graph (e) indicates, parsingadds another 50% on top of reading and decompression time. These costsare paid for all fields, including the ones that are not needed.

When few columns are read, the gains of columnar representation may beabout an order of magnitude. Retrieval time for columnar nested data maygrow linearly with the number of fields. Record assembly and parsing maybe expensive, each potentially doubling the execution time. Similartrends were observed on other datasets. A natural question to ask iswhere the top and bottom graphs cross, i.e., record-wise storage startsoutperforming columnar storage. In experience, the crossover point maylie at dozens of fields but varies across datasets and depends onwhether or not record assembly is required.

Map Reduce and the Described System.

Next an execution of MapReduce and the described system are illustratedon columnar vs. record-oriented data. In this case, a single field isaccessed and the performance gains are the most pronounced. Executiontimes for multiple columns can be extrapolated using the results of FIG.12. In this experiment, the average number of terms in a field‘txtField’ of table ‘T1’ is counted. MapReduce execution is done usingthe following Sawzall program:

numRecs: table sum of int; numWords: table sum of int; emit numRecs <-1: emit numWords <- CountWords(input.txtField)

The number of records is stored in the variable ‘numRecs.’ For eachrecord, ‘numWords’ is incremented by the number of terms in‘input.txtField’ returned by the ‘CountWords’ function. After theprogram runs, the average term frequency can be computed asnumWords=numRecs. In SQL, this computation is expressed as:

-   -   Q₁: SELECT SUM(CountWords(textile))/COUNT(*) FROM T1

FIG. 13 shows the execution times of two MapReduce jobs and thedescribed system on a logarithmic scale. Both MapReduce jobs are run on3000 workers (e.g., servers). Similarly, a 3000-node instance of thepresent system is used to execute Query Q₁. The described system andMapReduce-on-columns read about 0.5 TB of compressed columnar data vs.87 TB read by MapReduce-on-records. As FIG. 12 illustrates, MapReducegains an order of magnitude in efficiency by switching fromrecord-oriented to columnar storage (from hours to minutes). Anotherorder of magnitude is achieved by using the described system (going fromminutes to seconds).

Serving Tree Topology.

In the next experiment, the impact of the serving tree depth on queryexecution times is illustrated. Two GROUP BY queries are performed onTable T2, each executed using a single scan over the data. Table T2contains 24 billion nested records. Each record has a repeated fielditem containing a numeric amount. The field item.amount repeats about 40billion times in the dataset. The first query sums up the item amount bycountry:

Q₂: SELECT country, SUM(item.amount) FROM T2 GROUP BY country

It returns a few hundred records and reads roughly 60 GB of compresseddata from disk. The next query performs a GROUP BY on a text fielddomain with a selection condition. It reads about 180 GB and producesaround 1.1 million distinct domains:

Q₃: SELECT domain, SUM(item.amount) FROM T2 WHERE domain CONTAINS ’.net’GROUP BY domain

FIG. 14 shows the execution times for each query as a function of theserver topology. In each topology, the number of leaf servers is keptconstant at 2900 so that the same cumulative scan speed may be assumed.In the 2-level topology (1:2900), a single root server communicatesdirectly with the leaf servers. For 3 levels, a 1:100:2900 setup isused, i.e., an extra level of 100 intermediate servers. The 4-leveltopology is 1:10:100:2900.

Query Q₂ runs in 3 seconds when 3 levels are used in the serving treeand does not benefit much from an extra level. In contrast, theexecution time of Q₃ is halved due to increased parallelism. At 2levels, Q₃ is off the chart, as the root server needed to aggregatenear-sequentially the results received from thousands of nodes. Thisexperiment illustrates how aggregations returning many groups maybenefit from multi-level serving trees.

Per-Tablet Histograms.

The FIG. 15 shows how fast tablets get processed by the leaf servers fora specific run of Q₂ and Q₃. The time is measured starting at the pointwhen a tablet got scheduled for execution in an available slot, i.e.,excludes the time spent waiting in the job queue. This measurementmethodology factors out the effects of other queries that are executingsimultaneously. The area under each histogram corresponds to 100%. AsFIG. 15 indicates, 99% of Q₂ (or Q₃) tablets are processed under onesecond (or two seconds).

Within-Record Aggregation.

As another experiment, the performance of Query Q₄ is examined when runon Table T3. The query illustrates within-record aggregation: it countsall records where the sum of a.b.c.d values occurring in the record arelarger than the sum of a.b.p.q.r values. The fields repeat at differentlevels of nesting. Due to column striping only 13 GB (out of 70 TB) areread from disk and the query completes in 15 seconds. Without supportfor nesting, running this query on T3 would be expensive.

Q₄ : SELECT COUNT(c1 > c2) FROM (SELECT SUM(a.b.c.d) WITHIN RECORD ASc1, SUM(a.b.p.q.r) WITHIN  RECORD AS c2 FROM T3)

Scalability.

The following experiment illustrates the scalability of the system on atrillion-record table. Query Q₅ shown below selects top-20 aid's andtheir number of occurrences in Table T4. The query scans 4.2 TB ofcompressed data.

Q₅: SELECT TOP(aid, 20), COUNT(*) FROM T4 WHERE bid = {value1} AND cid ={value2}

The query was executed using four configurations of the system, rangingfrom 1000 to 4000 nodes. The execution times are in FIG. 16. In eachrun, the total expended CPU time is nearly identical, at about 300Kseconds, whereas the user-perceived time decreases near-linearly withthe growing size of the system. This result suggests that a largersystem can be just as effective in terms of resource usage as a smallerone, yet allows faster execution.

Stragglers.

Stragglers may be tasks (e.g., processing a tablet) that are notperformed, for example, because the machine performing the task has anoperational problem or the machine is not being aggressive enough inhandling the task given higher-priority tasks. Query Q₆ below is run ona trillion-row table T5. In contrast to the other datasets, T5 istwo-way replicated. Hence, the likelihood of stragglers slowing theexecution is higher since there are fewer opportunities to reschedulethe work.

Q₆: SELECT COUNT(DISTINCT a) FROM T5

Query Q₆ reads over 1 TB of compressed data. The compression ratio forthe retrieved field is about 10. As indicated in FIG. 17, the processingtime for 99% of the tablets is below 5 seconds per tablet per slot.However, a small fraction of the tablets take a lot longer, slowing downthe query response time from less than a minute to several minutes, whenexecuted on a 2500 node system. The next section summarizes experimentalfindings.

Section 8 Observations

FIG. 18 shows the query response time distribution in a typical monthlyworkload of the described system, on a logarithmic scale. As FIG. 18indicates, most queries are processed under 10 seconds, well within theinteractive range. Some queries have achieved a scan throughput close to100 billion records per second in a busy cluster, and even higher ondedicated machines. The experimental data presented above suggests thefollowing observations:

-   -   Scan-based queries can be executed at interactive speeds on        disk-resident datasets of numerous records;    -   Near-linear scalability in the number of columns and servers may        be achievable for systems containing thousands of nodes;    -   MapReduce can benefit from columnar storage just like a DBMS;    -   Record assembly and parsing are expensive. Software layers        (beyond the query processing layer) may be optimized to directly        consume column-oriented data;    -   MapReduce and query processing can be used in a complementary        fashion, one layer's output can feed another's input;    -   In a multi-user environment, a larger system can benefit from        economies of scale while offering a qualitatively better user        experience;    -   If trading speed against accuracy is acceptable, a query can be        terminated much earlier and yet see most of the data; and    -   The bulk of a web-scale dataset can be scanned fast, although        getting to the last few percent may increase the amount of        processing time.

FIG. 19 is a block diagram of a system for generating and processingcolumnar storage representations of nested records. The record generator1904 generates records of nested data from data sources 1920 and aschema 1902. The column generator 1908 receives as input the records1906 and the schema 1902 and outputs column stripes that represent thedata in the records 1906, but in a columnar format. The columnar data1910 may be queried in situ by the querying system 1912 in order toproduce different sets of output columns 1914. The columnar data 1910may also be assembled back into record form by the record assembler1916. The records 1918 that are output by the record assembler may eachinclude a sub-set of fields from the original records in the collection1906. The output records 1918 may be operated on by a record-based dataanalysis program (e.g., MapReduce).

More specifically, the data sources 1920 may include substantiallyunstructured data. Substantially unstructured indicates that the datamay include elements that denote structure, but the entire spectrum ofinformation may not be similarly structured. As an illustration, thedata sources 1920 may include the source code for each of millions ofwebsites. Although each website includes some degree of structure, thecontent of each website is not generated based on a common schema.Standards may generally govern a format of the site, but content andplacement of fields is not specified among each and every website by asingle schema. In some examples, the information in data sources 1920 isnot stored in the common storage layer 1922, but is pulled directly fromexternal sources on the internet.

The schema 1902 defines a common structuring for information that may becontained in the data sources. As described earlier in this document,the schema 1902 can require certain fields of information and may permitother fields of information to be stored as optional.

The record generator 1904 receives as input the schema 1902 andinformation from the data sources 1920. The record generator 1904 takesthe information from the data sources 1920 and structures all orportions of the information into individual instances of records thatcomply with the schema 1902. For example, while the data sources 1920may include substantially unstructured data from web pages, the recordgenerator 1904 may select pieces of information from each web page toinclude for particular records 1906.

Thus, each of the records 1906 may include data that is structuredaccording to the schema 1902. The structured data may include fields,which may denote a semantics of data values and a structuralrelationship of the data values. Accordingly, the schema may bereferenced to obtain additional definition information for the datavalue (e.g., what the digitally stored data value represents in the realworld or on a web page and relationships to other values).

Each record 1906 may include nested fields and data values. A nestedrecord may include more than one field of the same name or path. Thefields with the same name or path, however, can be structurally locatedin different locations in a particular record. For example, a singlefield that is defined by the schema may be able to repeat multipletimes. Further, fields may have children fields (i.e., nested fields).Thus, at a top level of a record a particular field may repeat, and eachrepetition of the field may or may not include a particular child field.In other words, the record may include instances of the child field insome portions of the record, but not in other portions of the records.

The collection of records 1906 may be translated into columnar data 1910to speed up processing of information in the records. For example, ifthe amount of records in the collection 1906 numbers in the billions,and each record could include hundreds of different fields, an analysisof the records may be time-intensive where information on a small numberof fields is desired. This is because each record in the collection 1906is stored with other information from the record. That is, each recordis grouped together in a consecutive portion of memory (e.g., asillustrated in the ‘record-oriented’ depiction of nested data in FIG.1).

In contrast, columnar data 1910 includes columns that each storeinformation for a single field in the schema 1902 (e.g., as illustratedin the ‘column-oriented’ depiction of nested data in FIG. 1). Thus, ifthe field is a byte long, the column for the field may be on the orderof billions of bytes (e.g., one byte for each record) as opposed tobillions of records (e.g., where each record may be a megabyte in size).The operations of the column generator 1908 are described in more detailin Section 4.2 “Splitting Records into Columns.” The storage format forthe columnar data 1910 is described in more detail in Section 4.1“Repetition and Definition Levels.”

The columnar data 1910 may be queried directly using the querying system1912. In other words, the columnar data 1910 may be queried withoutloading the data into a database. The querying system, when executing aquery, may receive as an input a table of columnar data. In someexamples, the querying system also receives as input the schema 1902.The columnar stripes may be stored together with the schema to make thedata self-describing. The querying system allows operations to beperformed on the columnar data in order to generate columns of outputinformation 1914. The output columns 1914 may include a subset of thevalues represented in the columnar data 1910, as determined by aparticular query. In some examples, the querying system outputs records1918 instead of, or in addition to, the columns 1914.

For example, the querying system 1912 may receive a first query and, inresponse, may parse through select columns of data and generate a set ofoutput columns that provides a title of all web pages that have one ormore videos and a number of the videos for each web page. The queryingsystem may receive a second query and in response output a second set ofoutput columns that provides a URL of every web page that was generatedwithin the last fifteen minutes. Other information from the columns 1910may not be included in a set of output columns that corresponds to aparticular query 1914.

Data that is stored as columnar data 1910 may need to be accessed by ananalytical service that does not operate on columnar data but operateson records. Thus, the record assembler 1916 may receive as input thecolumnar data and assemble records from the columnar data. The processof assembling records is described in more detail in Section 4.3 “RecordAssembly.”

Although the records may already be available in the collection 1906,the record assembler 1916 enables generating a set of records thatincludes a subset of the fields of the records in the collection 1906.For example, the records in the collection may include thousands ofdifferent fields. A user may want to run a record-oriented analysisprogram that only requires knowledge from two of the fields, but for allof the records. Thus, the record assembler 1916 may generate a set ofrecords that only includes information on the requested fields. Thisway, multiple sets of output records 1918 can be developed for differentanalysis or for different analysis programs. An analysis on smallerrecords may be faster than an analysis that must traverse the largerrecords that may be found in collection 1906.

The above description of the operation of the system 1900 illustrates anexample where the collection of records 1906 includes records that areformatted in accordance with the schema 1902, and where the columnardata 1910 is generated from this single set of similarly-structureddata. In various examples, multiple schemas 1902 may be used to generatea collection of records that includes many sets of differentlystructured records 1906. Each record, however, may identify in a headerthe type of schema that was used in the record's generation. Similarly,a column stripe may be generated for each field in each of many sets ofsimilarly-structured records. Each column stripe may indicate not onlythe name of the field, but also the schema from which the columnar datais associated (i.e., the schema used to format the records from whichthe columnar data was generated).

FIG. 20 is a flow chart of an example process for generating columnardata. The process may be performed by components of the system 1900.

In box 2002, a set of records is generated. The generation of therecords may be performed by the record generator 1904. Unstructured data(e.g., from data sources 1920) may be compiled into a standardizedrecord format that is defined by schema 1902. The records may be storedin the collection 1906.

In box 2004, the records in the collection 1906 are accessed. Forexample, the column generator 1908 receives as input the data from thecollection of records 1906.

In box 2006, a determination is made whether a column stripe is to begenerated for an additional field. For example, a stripe is to begenerated for each field in the set of records that are stored in thecollection 1906 (and thus each record in the schema 1902 or a subsetthereof). In this illustration, no stripes have been made so far, andthus there are fields for which a stripe is to be generated.Accordingly, the process proceeds to box 2008 in order to performoperations for a particular field. If all stripes had been generated(e.g., a stripe had been generated for every field in the collection ofrecords 1906), the process may end.

In box 2008, a list of values for the particular is generated. Forexample, each of the records may be traversed and a list of values forthe particular field is generated.

In box 2010, repetition levels for the particular field are generated.For example, the column generator 1908 may determine a repetition levelfor each of the values in the list by determining a most recentlyrepeated field in the path for the field.

In box 2012, definition levels for the particular field are generated.For example, the column generator 1908 may determine a definition levelfor each value (including values that are ‘missing,’ as described inmore detail above).

In box 2014, a columnar stripe is assembled for the particular field. Invarious examples, the repetition and definition levels are placed inpaired groupings in the header of the stripe. The list of values may beplaced in the body of the stripe.

In box 2016, the columnar stripe is broken into blocks that may becompressed. Each block may include a set of values and theircorresponding repetition and definition levels. Subsequently, adetermination in box 2006 of whether columnar stripes are to begenerated for additional fields is performed. If no additional columnarstripes are to be generated, the process ends.

The process depicted in FIG. 20 is an example process for generatingcolumnar stripes. Variations on the process are contemplated. Forexample, the operations of the boxes may not be performed sequentiallyas depicted in the flowchart. Stripes for multiple fields may begenerated at a single time. The repetition level and definition levelmay be generated as each value is obtained from a record. The columnarstripe may not be generated as a whole. Instead, each block may begenerated from the stripe and independently compressed. Thus, theflowchart may represent a conceptual mechanism for understanding thegeneration of stripes, but is not intended to be limiting. A process forgenerating columnar data is depicted in the algorithm of FIG. 4, whichmay not correspond to the operations described in relation to FIG. 20.

Web Service for Data Processing

FIG. 21 is a block diagram illustrating an example of a system 2100 thatimplements a web service for data storage and processing. In general,the columnar data processing system 2130 in the lower-right side of FIG.21 represents components of the system illustrated in FIG. 19 (whichillustrates a block diagram of a system for generating and processingcolumnar storage representations of nested records). As described inmore detail throughout this document, the columnar data processingsystem 2130 may execute efficient queries on columnar data that isstored in repository 2132. The remaining components of the data storageand processing service 2102 support a web service that stores data,allows external users (e.g., individuals accessing the service 2102 overthe internet) to import that data into tables, and, from the user'sperspective, perform queries over those tables. The data underlyingthose tables may be stored as columnar data and the queries over thetables may be implemented by the querying capabilities of the columnardata processing system 2130. These external users use ApplicationProgramming Interfaces (API) 2104, 2134, and 2124 to upload data to thedata storage and processing service 2102, import select portions of theuploaded data into tables, and perform queries on the tables. The datathat is stored in the tables may be replicated among multiple datacenters.

External users may use the Objects API 2104 to upload data into theobject storage 2106, potentially aggregating in a single service datathat streams regularly from many computing devices. External users maydefine tables and transfer the data that is located in the objectstorage 2106 to the tables. The transfer can be performed upon userrequest or automatically by the service 2102 as new data is uploaded tothe object storage 2106. The bulk data that is referenced in tables maybe stored as columnar data in storage 2132, while the metadata for thetables may be stored separately in the table metadata storage 2120. Theexternal users may run efficient queries on the tables using the QueryAPI 2124. The queries on the tables may be implemented as queries on theunderlying columnar data in storage 2132, and the processing of thequeries on the columnar data in storage 2132 may be performed by thecolumnar data processing system 2130, as described throughout thisdocument.

The object storage 2106 that is provided to external users through theObjects API 2104 is described in detail first. The object storage 2106hosts data that may be accessible through the Objects API 2104 tonumerous external users. As an illustration, more and more log data thatis generated by websites is being hosted in the cloud by remote servicesthat specialize in data hosting, as opposed to the websites themselvesstoring the log files on their own networks. Such cloud-based storagemay be particularly beneficial when data that is continuously generatedby many geographically dispersed computers needs to be aggregated in oneplace, available to multiple different users, and occasionally analyzed.

The object storage 2106 may include objects from a variety of users thatare grouped into buckets. Each bucket may be a flat container thatgroups objects and provides a unique namespace for the group of objects.An external user may own a collection of buckets and assign accesssettings to each bucket. Thus, objects in one bucket may be private to afew users while objects in another bucket may be publicly accessible onthe internet. The buckets may have a universally unique name among allbuckets owned by external users. In some examples, the buckets exist ina flat namespace such that the buckets are not nestable.

Each object may be stored as an opaque collection of bytes. In otherwords, the object storage 2106 may receive through the Objects API 2104different types of data, but may treat the received data as a chunk ofdata without regard to the format of the data. Each object may havecorresponding metadata that is stored in a separate table or database.Each object may be assigned to one bucket, and each object in a bucketmay have a name that is unique to the bucket. Thus each object may havea globally unique name when addressed with reference to the object'sparent bucket. Like buckets, each object may have its own access controllist, enabling sharing data over a network (e.g., the internet) betweena variety of users with different permissions.

The interface provided by the Objects API 2104 to exchange data may be aRESTful (REpresentational State Transfer) HTTP interface that employsindustry standard, or proprietary, protocols. As an illustration,external users may employ GET, PUT, POST, HEAD, and DELETE actions tointeract with objects that are stored in the object storage 2106. TheObjects API 2104 provides a sequential interface for writing and readingdata to objects in the object storage 2106. In some examples, theObjects API 2104 provides read-only access to some of the objects. Thus,a user may delete and replace objects, but may not incrementally modifyobjects. In some examples, the data storage and processing service 2102may not be configured for external customers to perform SQL-like querieson the objects directly. The data in the objects may be first placedinto structured tables before such queries are performed.

As an illustration, HTTP API requests may be received at the frontendserver 2126 from a remote computerized device that is associated with anexternal user. The frontend server 2126 forwards the request to an APIcollection implementor 2126. The API collection implementor stores APIlibraries, processes the request based on the stored libraries, andgenerates corresponding requests to the appropriate components of thedata storage and processing service 2102. Because API requests for theobjects API 2104 pertain to object storage 2106, the API collectionimplementor 2116 forwards a request to the object storage 2106.

The data storage and processing service 2102 provides the ability totransfer data that is stored in objects into tables and run efficientqueries on the tables using the columnar data processing system 2130.For example, users can append data to tables, create new tables, andmanage sharing permissions for tables. The data in the tables may bestored as columnar data in the columnar data storage 2132. Accordingly,when data is placed in a table, the data storage and processing service2102 transfers data from the object storage 2106 to the columnar datastorage 2132. The import job manager 2108 manages the process oftransferring the data and performs conversion operations on the data.

Each table represents a structured data set that a user may querythrough the Query API 2124. Users can create tables, import data intotables, share tables, run queries over the tables, and use the tables indata analysis pipelines. The external user exposure to a table may be anobject that is stored in the object storage 2106 as a delegate object. Adelegate object may be an object that provides an interface to a set ofdata and operations that are not stored in the object storage 2106. Inother words, delegate objects may allow tables to be mapped into thenamespace for the object storage 2106. Thus, each table's name mayreside in the global object namespace and may be unique. A delegateobject for a table may hold metadata that identifies the owner of thetable, the access control list for the table, and the table identifier(which links the delegate object to additional table metadata, and isdescribed in more detail below).

Thus, in one implementation, an external user sees tables as objectsresiding within buckets. The user may view a list of all tables in abucket and may delete a table by deleting the corresponding delegateobject, much in the same way that the user may view a list of objectsand delete objects. When an external user makes a request thatreferences a table via its object name, a reference to underlying tabledata is extracted from the delegate object and is used to service therequest. For example, a delete operation on a table may trigger cleanupoperations on the corresponding metadata in the table metadata storage2120 and underlying columnar data in the columnar data storage 2132.

A table is created in response to a request through the Table API 2134.The table management system 2118 may create a table at a key of adatabase in the table metadata storage 2120, and then create a delegateobject in the object storage 2106 to reference the key. The tablemetadata storage 2120 may hold metadata for the tables that arereferenced by delegate objects. For example, a table identifier in adelegate object references a key in a row of the table metadata storage2120. The table metadata storage 2120 stores, for the table and underthe key, any combination of: (1) the table identifier, (2) a tablerevision number, (3) a table name, (4) a data reference set, (5) aschema description, and (6) data statistics.

The table name may be a back pointer to one or more buckets and objectsthat the table is associated with. Storing the table name may facilitategarbage collection and help avoid conflicts if the table is deleted anda new table with the same external (object) name is later created. Thedata reference set may include path references to the columnar data 2132that backs the table (e.g., that stores the bulk data for the tables).The schema description may allow for efficient schema validation duringdata management operations. The data statistics may identify informationabout the table, for example, a number of rows, a size of datareferenced by the table, and a last updated timestamp.

In some examples, a table is filled with data from objects in the objectstorage 2106 in response to a demand by a user (e.g., a Table API call).For example, the import job manager 2108 may receive an ad-hoc requestfrom a data owner to import the data from a set of objects from the datastorage 2106 into a table. In other examples, a data owner may generatea job that is executed by the import job manager 2108, and thatestablishes a continuous import service that takes objects that arenewly placed in a bucket and “auto-imports” the data in the objects intoa table. After the data from the objects is imported into the table, theobjects may be automatically deleted without user input.

The import job manager 2108 receives requests to import data from anobject into a table, and in response performs several operations totransfer data to the columnar data storage 2132. The job manager 2108creates job metadata to track the import and launches a coordinator2110. The job metadata is stored in the import job metadata storage2122.

In particular, the import job manager 2118 may aggregate the content ofobjects, perform data format transformations, shard the data intoappropriately sized chunks, move the data into a different storagelayer, and place the chucks of data in the columnar data storage 2132for access by the columnar data processing system 2130. In someexamples, the import job manager 2108 transforms the object data intocolumnar data. In other examples, the import job manager 2108 placesnon-columnar chunks of data in the columnar data storage 2132, and thecolumnar data processing system 2130 converts the non-columnar chunks ofdata to a columnar format.

The coordinator 2110 is invoked by the import job manager 2108 toanalyze an import job and launch an appropriate number of workers toprocess the data in a reasonable amount of time. The coordinator 2110analyzes the input data objects and decides how to assign the dataobjects among individual workers 2112 that process the input dataobjects. The coordinator 2110 spawns individual worker instances andobserves worker progress. The coordinator 2110 ensures that the datahandled by each worker is not too small or large.

In some circumstances, use of a single coordinator and many workers mayenable the import job manager 2110 to scale with data size and a numberof input data objects. If a failure is detected or a worker isinefficient, the worker may be restarted or the worker's tasks may bereassigned. Each worker instance 2112 may sequentially read input dataobjects, perform appropriate format conversions, and store the data insharded bundles of columnar data 2132. In some examples, workerinstances are assigned to run in the same clusters where the input datais located (because cross-datacenter traffic can be inefficient andexpensive).

The workers convert data from a given set of inputs into a sharded setof columnar data bundles, and appends the bundles to the appropriatetable. Input data may be any schematized data format that the systemunderstands. Input data may be text or binary form, and the schema maybe incorporated in the data format or specified along with the data.Example input data may be: (1) a record data type (a self-contained andself-describing structure for record-stored data), (2) a column datatype (a self-contained and self-describing structure for column-storeddata), (3) text based formats for which the data storage and processingservice 2102 knows the schema (field separated or fixed field lengthformats such as Apache, AppEngine, or W3C logs), or (4) text basedformats that can be described by name/type value pairs (field separatedor fixed field length, and the user specifies the name/type pairs andseparators or field sizes).

The coalescer and garbage collector 2114 may periodically scan tablesfor issues to fix. The coalescer may monitor contents of the columnardata storage 2132 and detect columnar data bundles that are too smalland may be coalesced into larger bundles. The garbage collector detectscolumnar data bundles that are not referenced by any tables and may bedeleted. Similarly, dangling table metadata may be cleaned up, forexample, when a table is generated but the table creation process failsbefore a corresponding delegate object is generated in the objectstorage 2106.

Once a table has been created and data has been imported into the table(e.g., by generating table metadata, generating the delegate object inthe data storage 2106, and generating corresponding columnar data 2132),user queries may be run on the tables. The queries may be SQL-like andmay be received from external users through the Query API 2124. Thefrontend server 2126 receives the Query API requests and forwards therequests to the API collection implementor 2116, which passes thequeries to the query manager 2128.

The query manager 2128 takes SQL-like queries and an authenticated tokenfor the external user, verifies that the external user can access thetables referenced in the query, and hands the request off to thecolumnar data processing system 2130 and table management system 2118for executing the query. As described earlier in the document, thecolumnar data processing system 2130 may query columnar data 2132 andoutput result data (e.g., columns of result data). The result data maybe placed in a table defined by the query, returned to the external userin a format defined by data format templates, or placed in an objectdefined in the API call.

The API collection implementor 2116 handles API calls through theObjects API 2104, Table API 2134, and the Query API 2124. Example APIfunctions are detailed later in this disclosure. The collection of APIsmay enable SQL-like summaries to be performed on large quantities ofdata that is imported into tables from the object storage 2106. Thesource objects in object storage 2106 may be aggregated from numerousweb sources, each source having permission to place data in the samebucket of object storage 2106. Thus, the illustrated data storage andprocessing service 2102 can provide an aggregation and data importpipeline for the columnar data processing system 2130 described earlierin this document. The columnar data processing system 2130 can providefast queries and aggregations of large datasets.

FIG. 22 is a flowchart showing an example of a process 2200 forperforming data storage and processing. The process 2200 may beperformed by the system illustrated in FIG. 21, and more particularlythe data storage and processing service 2102.

In box 2202, a request to store data is received at a server system. Forexample, a server system that provides the data storage and processingservice 2102 may implement an API that enables remote computing devicesto upload data to the server system, for example, over the internet. Theserver system may receive a function call to upload data through the APIand from a remote computing device. The function call may identify datato upload and a name for the data. The name for the data may identify astorage location for data (e.g., a bucket).

In some examples, the request may be received from a computing devicethat does not access the data storage and processing service 2102 overthe internet. For example, a third party may physically ship one or morephysical storage devices (e.g., CDs, DVDs, hard discs, or RAIDenclosures) to a business entity that operates the data storage andprocessing service 2102. Employees of the business entity may load thedata that is included in the physical storage device into the objectstorage 2106 using a computing device that is connected to the datastorage and processing service 2102 over a local network. The localtransfer of data to the object storage 2106 may not use the API.

In box 2204, the identified data is stored as an object in a repositoryat the server system. The repository may include a collection of“buckets” that are each configured to include one or more objects. Eachbucket may have a name that is unique among the collection of buckets,and the objects in each bucket may have names that are unique to thebucket. Thus, each object may be addressable by a unique name path(e.g., bucketName.objectName). Each bucket may be owned by one or moreexternal customers.

Storing the data (e.g., a record, collection of records, file, orcollection of files) as an object may include determining that theremote device that is uploading the data is authorized to place objectsin the identified bucket. For example, an external customer may create abucket and assign specific user accounts as authorized to place data inthe bucket and view the contents of the bucket. If a remote devicelogged in under one of the specific accounts requests to place data inthe bucket, the request may be granted. Similar requests bynon-authorized user accounts may be rejected.

In box 2206, a request is received to create a table. For example, thedata storage and processing service 2102 may receive from a remotecomputing device an API function call requesting to create a table. Atable may be a structured data set that a user may query. The requestmay define a name for the table, and define the fields for the table.For example, the request may include a schema that defines a structurefor a type of record, and the table may be generated to store data forrecords of the type.

In box 2208, the table is created. For example, metadata for the tablemay be added under a row in a database. A delegate object thatreferences the table row may be placed in the object repository. Forexample, if the API call requests to generate a table namedbucketName.TableName, a delegate object that is named TableName may beplaced in the bucket bucketName. The TableName delegate object mayinclude an access control list for the table and a table identifier(e.g., an identifier of the database row that stores metadata for thetable).

In box 2210, a request to import data in the object into the table isreceived. For example, the data storage and processing service 2102 mayreceive from a remote computing device an API function call requestingthat data in the object be loaded into the table or appended to the endof a table that already includes data. In some examples, the request isreceived from a continuous import service. The continuous import servicemay periodically monitor a bucket and when the bucket includes newobjects (e.g., when external customers place new objects in the bucket)the continuous import service requests that data in the new objects beappended to the table. In some examples, an API function call thatestablishes the continuous import service was received earlier. Thecustomer-facing view of the continuous import service may be a delegateobject.

In box 2212, the data in the object is converted into columnar format.For example, the object may include a set of records, and the recordsmay be converted into a set of columnar stripes, where each stripe (orset of blocks of a stripe) describes a single attribute field of therecords. The columnar stripes may include the repetition and definitionlevels described throughout this document.

In box 2214, the columnar data is stored in a repository. In someexamples, the repository for the columnar data is different than therepository for the objects. For example, the repositories may bedifferent storage layers implemented at a server system implementing thedata storage and processing service 2102. Metadata that references thecolumnar data may be stored in the table database. Thus, a query of thetable may include referencing the metadata in the table database toidentify columnar data that corresponds to particular attribute fieldsfor the data in the table.

In some examples, the request identifies several objects for which datais to be loaded into the table. The data content of the objects may beaggregated, data format transformations may be performed, the data maybe sharded into appropriately sized chunks of columnar data, and thechucks of columnar data may be placed in the repository for columnardata.

In box 2216, a request is received to perform a query on the table. Forexample, the data storage and processing service 2102 may receive an APIfunction call from a remote computing device requesting that a SQL-likequery be run on the table. In some examples, the query operates on thetable and one or more other tables. For example, the query may collectdata having particular characteristics from each of two tables and placethe aggregated data in a third table.

In box 2218, a determination is made whether the remote computing devicerequesting the query is authenticated to access the one or more tablesspecified in the query. For example, the delegate object for each tablemay have an access control list that identifies user accounts that mayrun queries on the table corresponding to the delegate object, deletethe table, and add data to the table. If a remote computing deviceassociated with a user account attempts to run a query on severaltables, the data storage and processing service 2102 determines if theuser account is authorized to query each of the tables.

In box 2220, a query is performed on columnar data. For example, queriesare performed on the columnar data underlying the tables specified inthe query request. A query manager 2128 may generate, based on the queryreceived from the remote computing device, component queries that areperformed on particular columns of data for the tables specified by thequery. For example, the query may request data within a particular rangefor a single attribute in a table. The query manager 2128 may generate acomponent query on a collection of blocks of a columnar stripe that isassociated with the single attribute, and may run other componentqueries on columnar stripes for other of the attributes in the query.

In box 2222, data is output based on the query. For example, the querymay identify a table that the results of the query are to be placed in.Accordingly, the query manager 2128, table management system 2118, andcolumnar data processing system 2130 may place the result data in atable. Also, the query may identify one or more objects in which toplace the results of the query. Thus, the results of the query may beplaced in one or more objects in the object storage 2106. The API callrequesting that the data be placed in the object may specify a dataformat for storage of the data in the object. Accordingly, outputtingthe data as one or more objects may include converting output columns ofcolumnar data stored in the columnar data storage 2132 into a differenttype of data format for storage in the object storage 2106 (e.g., arecord-based type of data format).

In some examples, the schema may be extensible. In other words, a thirdparty may request minor changes to the schema (e.g., by editing theschema through an API or uploading a new schema that includes minorchanges). The minor changes can include adding new optional fields tothe schema. In some examples, however, the user may not be able to addnew required fields or remove existing required fields from the schema.The schema may be updated without rebuilding or regenerating the entiredata set. As such, a new columnar stripe may be added for a newly addedoptional field without modification of the existing columnar stripes.

In some examples, a third party user may be able to change field namesand add aliases for field names. For example, a schema may include afield that is named “Time.” A third party user may decide to change thename of the field to “LocalTime.” As such, newly submitted data recordsmay include “LocalTime” fields, while data records that are alreadystored by the data storage and processing service 2102 may include“Time” fields. The data storage and processing service 2102 mayrecognize the fields “Time” and “Local Time” as aliases of each other.As an example, the data storage and processing service 2102 may store anindex that matches field names to a unique identifier for a dataelement. The index may associate both the “Time” and “Local Time”aliases to a unique identifier for a field (e.g., the identifier“1A452BC”). As such, the unique identifier may be associated with anddesignate a single columnar stripe that all the data values for the“Time” and “Local Time” fields. In some examples, the data recordsstored in the object storage 2106 also identify fields with uniqueidentifiers and do not identify fields with names that can change.

Also, the data may be directly output to the remote computing device,additionally or instead of placing the data in a table or an object. Forexample, a remote computing device requesting the query may in responsereceive the results of the query. The results of the query may be invarious formats (e.g., CSV or data for reconstructing a display of theoutput table).

Replication and Query Processing

In various examples, the data that is stored by the data storage andprocessing service 2102 is replicated among geographically dispersedserver devices. For example, an object that is stored in object storage2106 may be replicated among server devices in data centers that arehundreds of kilometers apart from each other. Thus, a localized serverfailure, power outage, or natural disaster may not influence theavailability of the object. Similarly, after the data in the object hasbeen imported into a table, the columnar stripes that underlie the tableand that reside in the columnar data storage 2132 may be replicatedamong geographically dispersed server devices. The table metadata thatis stored in the table metadata storage 2120 may also be replicatedamong geographically dispersed server devices. This replication ofcolumnar data and table metadata is described hereinafter.

FIG. 23 shows a schematic diagram of an example computing systeminfrastructure. In this illustration, an organization operates multipledata centers, although only two data centers 2310 and 2350 are shown inthe figure for illustrative purposes. Each data center may be separatedby a geographical distance of, for example, at least fifty kilometers orat two hundred kilometers. Each data center includes numerous computingdevices (e.g., servers, groups of servers, or individual CPUs). Thecomputing devices are logically grouped into cells. For example, Cell A2312 is shown as including three computing devices 2314, 2316, and 2318,although the cell may include additional computing devices.

Each computing device in a data center may belong to a single cell(e.g., no computing device may be assigned to more than one cell andservice more than one cell at particular times). The devices that areassigned to a specific cell, however, may dynamically change so as tobalance resource allocation among the cells of a data center. Anindividual or group of individuals (e.g., a project team) at theorganization may reserve one or more cells at one or more data centers,for example, to support data processing that the team requires to offera web service. A reserved cell may exclusively support the reservingteam and may not be available to other teams while reserved. Asdescribed with further detail below, a team may reserve a collection ofcells from different data centers, and may implement a data replicationprocess among the cells.

As indicated previously, the data that is replicated among the cells maybe the columnar data that is described with respect to columnar datastorage 2132. The system may replicate the columnar data in order toensure that the columnar data is persistent in the event that aparticular device or data center crashes, in order to support loadbalancing among the data centers, and/or in order to reduce latency inproviding a customer with a result to a query by processing data at adata center that is geographically near to the customer.

FIG. 24 shows a schematic diagram of an example of a data namingstructure that may be used to support replication of data. In thisexample, a block of columnar data may be assigned a logical address. Thelogical address may be distinct from physical addresses of thereplicated copies of the columnar data, and may not reference specificmachines or locations at which the columnar data is replicated. Thelogical address may take the form Cohort:Instance:Shard:File, where eachportion of the “Cohort:Instance:Shard:File” logical address indicates alogical set of data that can be replicated among multiple cells. Forexample, a cohort may be a rather sizable logical collection of datathat is replicated among multiple cells. The “file” portion of theaddress, however, may represent a logical name for a single block ofcolumnar data. The use of the term “logical” herein indicates a namingstructure that is abstracted from the physical address of the data, andthat can reference data that is replicated among multiple physicallocations. As such, a logical set of data may be associated withmultiple actual copies of the set of data at multiple respective cells.

The logical collection of data denoted a cohort may include multiple“instances” that represent logical portions of the sizable collection ofdata. Each of the instances in the cohort may be replicated among themultiple cells that replicate the data in the cohort. For example,cohort 2400 is assigned instances 2410, 2420, and 2430. Each instancerepresents a partition of the data that is stored by the cohort and thatis replicated among the different cells that store the data in thecohort.

Although each cell that stores data in a cohort may store a copy of allinstances that, put together, comprise the cohort, each instance may beassigned one of the cells as its writing cell. For example, cell 2440 isassigned as the writing cell for instance 2410, cell 2450 is assigned asthe writing cell for instance 2420, and cell 2460 is assigned as thewriting cell for instance 2430. A writing cell may be the initial cellthat writes to its corresponding instance. Once the instance has beenwritten to by its respectively assigned writing cell, however, thewriting cell updates the other cells with the instance of the cell.Along the same line, the writing cell receives updates to itsnon-writing instances from the other cells in the cohort.

In other words, although Cell 2440 may be the writing cell for Instance2410, cell 2440 may store replicated data from instances 2420 and 2430.As such, the writing cell for an instance may be the cell that firstupdates information for that instance of data. As a result, whilemultiple cells may store data from a cohort, the cell that first writesan update to a file in the cohort may be determined by which instancethat file is logically stored within. This process is illustrated inFIG. 24 by the narrow, solid arrows (e.g., arrow 2470) and the large,hollow arrows (e.g., arrow 2480). The small arrows represent the writingof data from the cell to its assigned instance. The large arrowsrepresent the replication of updates from the other cells and of otherinstances back to that particular cell. For example, cell A 2440 writesto cell 2410 but receives replicated data from cells 2420 and 2430.

Each instance may be written to as a direct result of a queryingoperation by only its assigned writing cell. All other cells that storethe instance may copy the information that resulted from the queryingoperation from the assigned writing cell. These other cells may not copythe information that resulted from the querying operation until thatinformation has been written to the appropriate data structures of theassigned writing cell. As such, even though all cells that replicatedata in a cohort may store a copy of an instance from that cohort, asingle one of the cells that is assigned to the instance may bedesignated as a first cell to write to its locally-stored copy of theinstance. As described in greater detail below, the other cells maythereafter update their versions of the instance.

Each instance includes multiple shards. A shard represents a logicalportion of the logical files that are stored in the instance. The use ofshards permits the system to scale its replication process by assigningmultiple replication jobs to multiple respective shards at a givenmoment.

As mentioned above, the cohort:instance:shard:file naming structureprovides a logical address for data that is distinct from a physicaladdress for the data. For example, a cohort may be serviced by threecells: a first cell in North America, a second cell in Europe, and athird cell in China. The development team that has reserved the cellsassigned to the cohort may determine that customers would be betterserved if a cell in India were used to support the cohort instead of thecell in China. As such, the cell in India may be brought online and thecell in China may be brought offline. Aside from the period in whichdata is transitioned from the cell in China to the cell in India, theoverall number of cells that support the cohort may not change. Further,the cohort:instance:shard:file name for data that was initially writtenby the China cell may remain the same even after writingresponsibilities have been transferred to the Indian cell. Indeed aparticular file named according to the cohort:instance:shard:fileprotocol may be stored at the three cells that back the Cohort.

FIG. 25 shows a schematic diagram of components that may be used toprovide the data processing service. The figure shows system componentsfor servicing a single cohort (e.g., Cohort 2400). In this example, thecolumnar data that backs the table is stored in cohort 2400, which isstored by cells A, D, and G. Each cell can include its own data cache,which may be provided by local storage systems of the individual cells,and the data cache can store the replicating data. For example, eachcell may execute an instance of the local storage system. The localstorage system may be a scalable distributed file system, such as theGoogle File System. Thus, the files or columnar data describedthroughout this document may be replicated among data caches 2534, 2544,and 2554 of cells 2440, 2450, and 2460, respectively.

As discussed with reference to FIG. 24, each cell's data cache can storeall instances that are assigned to the cohort. Each cell, however, maybe designated to write to one instance (or more than one instance when,for example, another one of the cells is brought off line forservicing). In FIG. 25, the writing cell that is designed to write to aparticular instance is identified by the underline of the label for eachparticular instance. For example, cell A 2440 writes to instance 2410,cell D 2450 writes to instance 2420, and cell G 2460 writes to instance2430.

In this disclosure, the designation “writing cell” for a particularinstance indicates that the cell has been established, at leasttemporarily, by the overall storage system as a first cell in the cohortto write to the particular instance. Thus, during a particular period oftime (e.g., one minute, one hour, or one day), all initial writingoperations to a particular instance may be performed by the writingcell, and the updated data may thereafter be replicated to the othercells. Writing operations during the same period of time to otherinstances may be performed by their other writing cells. As such, thewriting and replication operations are distributed among the cells ofthe cohort.

The cell that writes to a particular instance may not write to otherinstances in the cohort, even though the cell may store the otherinstances. For these other stored instances, the cell updates the datain the stored instances with data from the writing cells for therespective instances. For example, Cell D 2450 updates instance 2410from Cell A 2440 and updates instance 2430 from Cell G 2430. For aparticular instance, all cells in a cohort except for the writing cellmay receive updates to the particular instance from the writing cell.

A single instance of a querying subsystem (e.g., subsystem 2532) canexecute at each cell in the cohort. In other words, the different cellsor data centers may execute different manifestations of the queryingsubsystem. Stated even another way, each occurrence of the queryingsubsystem (e.g., occurrence 2532) can perform data processing operationson a single data cache (e.g., data cache 2530). As such, all columnardata that is accessed during a data processing procedure may be storedwithin a single data cache at the same data center at which the dataprocessing procedure executes. In this illustration, querying subsystem2532 represents a manifestation of the columnar data processing system2130 (FIG. 21) and the data cache 2534 represents a copy of the columnardata storage 2132 (FIG. 21).

The system also may include a global storage system 2510. The globalstorage system may be a storage system that includes its own internalmechanisms for replicating data across data centers and cells. The cellsthat support the global storage system may be different than the cellsin which the columnar data processing operations are supported. The datacenters that store the columnar data and the data backing the globalstorage system 2510, however, may be at least partly in common.

The described data processing system may not use the global storagesystem 2510 for all storage and data processing operations because thecolumnar data processing service may be optimized to operate on datathat is stored within a single data center, for example, by a singledata cache 2534. Moreover, the columnar data processing service may usegreater internal data processing throughput (e.g., data processing andcommunication among computing devices of a storage system) than externalthroughput (e.g., communication throughput between a storage system anda device of an external user through an application program interface).Storage systems that are localized to cells may provide betteroperational characteristics for a processing system that uses greaterinternal throughput than external throughput.

The global storage system 2510 can include global file storage 2512(e.g., for providing persistent storage for data). The global storagesystem 2510 can also include a global table 2514. The global table 2514is used to store customer projects, datasets, and tables, for example.The information that is stored in the global table 2514 is described ingreater detail below.

Each cell also may include a cache state (e.g., cache state 2536). Eachcache state may identify the state of the data that is stored by theuser-generated tables (e.g., in the columnar data) in all of the datacaches of a cohort. For example, each cell may have, in its respectivecache state, information that identifies the present state of eachinstance in the cohort for each cell assigned to the cohort. Theinformation may also identify the present state of each file in thecohort (e.g., each block of columnar data), and may identify whether thefile is up to date (e.g., whether the cell still needs to receive anupdate from the writing cell for the instance that stores the file). Thecache state information may replicate quickly between cells. Theunderlying data (e.g., the columnar data), however, may not replicate asquickly. As such, in some instances, each cell may query the informationin its cache state to identify whether to replicate data from anothercell. In other instances, each cell distributes updates for its assignedwriting instance to other cells.

Each cell can include a writing subsystem (e.g., writing subsystem3540). The writing subsystem handles writing operations to the instancethat is assigned to the cell at which the writing subsystem executes.The writing subsystem can either parse the received query to identifyvalues that are to be added to the instance that is assigned to thecell, or can receive such values from the querying subsystem 2532 andwrite the values to the local copy of the instance. In some examples,the writing subsystem modifies portions of the instance by appendingvalues specified by the query into certain blocks stored by theinstance. In some examples, the writing subsystem modifies portions ofthe instance by replacing certain blocks in the instance withnewly-formed blocks that include the values specified by the query.

Each cell can include a replicating subsystem (e.g., replicatingsubsystem 2538) that handles the replication processes for the cell. Asdescribed with respect to FIGS. 29 and 30A-C, the replicating system caneither (i) compare copies of instances stored by the cell to copies ofthe instances stored by other cells to determine if the other cells havestored more up-to-date copies of the instances (as described withrespect to FIG. 29), or (ii) can, after the local manifestation of thewriting subsystem 3540 updates an instance, distribute the updates tocopies of the instance at other cells (as described with respect toFIGS. 30A-C).

A query distributor 2542 can receive a query that is structured toselect data from a table, and can distribute the query to one of thecells (e.g., one of cells 2440, 2450, and 2460) based on the replicationstate of the data stored by the cells and various other criteria, suchas distances between a remote computing device that submitted the queryand the cells.

FIG. 26 shows a schematic diagram of an example of the components storedwithin the global table and how these components relate to auser-generated table. User-generated table 2635 conceptually illustratesa table as the table may be understood and perceived, at least in part,by external users of the web service. The user-generated table 2635represents tables described throughout this disclosure, for example, thetables described with reference to FIG. 21 and the table API 2134, thetable management system 2118, and the table metadata storage 2120.

The user-generated table 2635 may be backed by columnar data. Theuser-generated table 2635 may be created by the data processing systemin response to a method call received through an API. The method may becalled in response to the external user providing user input for callingthe method on a remote device, or may be called by an automated systemthat the external user had set up to access the data processing servicethrough the external user's account.

User-generated table 2635 includes multiple rows 2612 and multiplecolumns 2614. For example, each row may represent a product that is soldby an online retailer and each column may represent a feature of theproducts (e.g., price, quantity available, and description). Asdescribed above, each column of data that backs the table may besegmented into blocks. The blocks for different columns may representthe same collections of rows. For example, the data in the table may bebroken into subsets of the rows (e.g., rows 1-3, 4-6, and 7-9), but withmultiple attributes for each subset of rows (e.g., each subset of rowsincludes data for columns A, B, and C). Each subset of rows may beseparated into blocks that each represents data for a single attribute.Accordingly, each block can represent values for a subset of rows for asingle attribute.

As described earlier, each block may be stored within a particularinstance in a cohort as a logical file. As such, a block may bereplicated among the multiple cells of a cohort that store thatinstance. FIG. 26 illustrates that Block A 2616 is stored by Cell A2440, Cell D 2450, and Cell G 2460, as described in greater detail withrespect to FIG. 25.

The global table 2514 stores data that identifies the nested structureof user data. The user data may be stored in the nested relationshipProject:Dataset:Table:Storage_Set:Storage_Entity. Each level of thenaming structure may provide a unique namespace. As an illustration, acustomer may have one or more projects (not shown in FIG. 26). Eachproject can group many different datasets and provide a separate billingunit for each dataset. As such, different departments at a customerorganization may use different projects so that the provider of the dataprocessing service can bill the departments separately for dataprocessing usage.

Each project can include one or more datasets. A dataset is a collectionof tables to which an access control list can be attached. As such, anexternal user may group multiple tables under a dataset so that the usercan provide other users access to the entire set of tables withouthaving to grant the other users access to each table individually.

At least from the perspective of external users, data processing isperformed on tables within each dataset (e.g., table 2635). As describedlater in this disclosure, users can create tables, add data to tables,and query tables. Each table includes multiple storage sets (e.g.,storage set 2640). A storage set can be a container for multiple storageentries. A storage entry may be a representation in the global table2514 of a block. For example, storage entry 2650 is a representation ofBlock A 2616. Each storage entry can include an identification of itsrespective block (e.g., a logical address of the block in the formCohort:Instance:Shard:Block), an identification of each cell that isstoring the block, and an identification of the cell that is designatedas a writing cell for the block.

An example structure for a StorageSet follows:

CREATE TABLE StorageSet { required string datasetId; required stringtableId; required string storageSetId; enum State {   TENTATIVE = 1,  COMMITTED = 2,   GARBAGE = 3  }  required State state; }PRIMARYKEY(datasetId, tableId, storageSetId) IN TABLE ProcessingServiceTable,ENTITY GROUP KEY (datasetId) REFERENCES ProcessingServiceDatasets;

Example metadata for a StorageEntry follows:

CREATE TABLE StorageEntry {  // All the keys in StorageSet.  requiredstring storageEntryId;  enum StorageType {   LOCAL_STORAGE_REPLICATED =1,   CACHED_BLOB = 2   — — —  }  required StorageType type;  // Commonstorage labels.  optional string cohort;  optional string instance;  //Type specific info.  // LOCAL_STORAGE_REPLICATED  optional string shard; repeated string relative_file  optional string relative_pattern;  //CACHED_GLOBALBLOB  repeated message<BlobRef>blobref; }PRIMARYKEY(datasetId, tableId, storageSetId, }PRIMARY KEY(datasetId, tableId,storageSetId, storageEntryId) IN TABLE StorageSet, ENTITY GROUP KEY(datasetId) REFERENCES ProcessingServiceDatasets;

FIG. 27 is a schematic diagram showing examples of locally storedtables. Each such local table may be stored at a data center, oralternatively at a cell. In other words, each data center may have asingle table, or each cell in the data center that is used for the dataprocessing service may have its own table. Each local table can identifythe state of data that is stored at the respective cell. Each localtable can further identify the state of data that is stored at the othercells in the cohort.

As an illustration, local table A 2710 stores the cache stateinformation (e.g., the cache state 2536) for cell A 2440. The cachestate information may include status information for each table that isstored by the data cache of cell A 2440 (e.g., “User Table A (Cell A)Status” 2720 and “User Table B (Cell A) Status”). The cache stateinformation may also include information for each table that is storedby the data cache of the other cells in the cohort (e.g., “User Table A(Cell D) Status” and “User Table B (Cell D) 2730”).

The status information for each user table may include the date that thecolumnar blocks supporting the table were cached, a most-recent datethat the columnar blocks supporting the table were accessed, and a datethat the table was deleted. In some implementations, the data identifiesthe date cached, accessed, and deleted for individual blocks in a table.The status information may indicate whether each columnar block is up todate, for example, as a result of a determination whether the date thata particular block was cached is more recent than a date that theparticular block was cached at the writing cell (assuming that such adetermination is taking place for a cell that is not the writing cell).In some implementations, the system does not permit updating a blockonce created. Rather, in order to effectively update a block, the systemmay have to delete the existing block and add a new block that includesthe updated data. In some implementations, the above-describedinformation is stored by the global table 2514.

FIG. 28 is a flowchart showing an example of a process for reading datafrom a table with a query.

In box 2802, a computing system receives a query that is structured toselect data from a database table. For example, the query may bereceived as part of an API call, as discussed in greater detailthroughout this disclosure. The computing system may be a frontendcomputing system that receives the API calls. The computing system mayinclude computing devices at a datacenter at which one or more of thecells that store the columnar data are physically located. The query maybe a request to insert one or more data values into a database table.

In box 2804, the computing system identifies that the data that is to beselected from the database table is stored by one or more blocks. Thecomputing system may identify an instance in which the one or moreblocks are stored, for example, by obtaining from the global table alist of blocks that back a table identified by the query and identifyingthe instance that stores the blocks. For example, the computing systemcan access the global table 2514, identify the query-identified tablefrom a list of tables, can identify a list of the storage entries thatsupport the user-identified table, and can obtain a list of the blocksfor each storage entry. The obtained list of blocks may include thelogical address of each block (e.g., cohort:instance:shard:file) and/orthe physical address of each block (e.g., cell:filename).

As discussed throughout this disclosure, the identified instance may beone of multiple partitions of data that comprise a cohort. The termscohort and instance are not intended to limit the scope or type of datastorage structure, and may alternatively be referred to within thisdisclosure as a logical collection of data (e.g., a cohort) and alogical partition of the logical collection of data (e.g., an instance).

In box 2806, the computing system identifies multiple cells among whichthe blocks are replicated. For example, the computing system mayidentify, in the storage entry for each of the blocks, the cells ofcomputing devices at which each of the blocks are replicated. Becauseall blocks in a table may be replicated among the same cells, thecomputing system may only have to identify this information from one ofthe storage entries. Because the replication process may be structuredso that each data center has a single cell that supports the replicationprocess, this disclosure at times may refer to the replication on a datacenter-by-data center basis, rather than a cell-by-cell basis, eventhough the replication may be among cells of the data center rather thanall computing devices that comprise the data center.

In box 2808, the computing system identifies whether any cells havefully replicated the one or more blocks. In other words, the computingsystem identifies whether any of the cells have copies of the one ormore blocks in their most up-to-date form (e.g., so that no other cellmore-recently stored a copy of any of the cells in a different form). Insome examples, the storage entry for each block may include, for eachcell that stores the corresponding block, a flag or other indication ofwhether the cell stores a copy of the block that is fully up-to-date.

In some examples, the computing system analyzes data stored by thestorage entry for each block to determine which of the blocks may befully up-to-date. For example, the computing system may compare a datecached timestamp for the copy of the block that is stored by the writingcell for that block to the date cached timestamps for each of the othercells that replicate the block. If a block at one of the other cells hasa date cached timestamp that is more recent than the date cachedtimestamp for the copy of the block at the writing cell, then thecomputing system may indicate that such the block at the one of theother cells is up-to-date. If all of the one or more blocks for a cellare identified as being up-to-date, the computing system may indicatethat the one or more blocks are fully replicated.

In some examples, the writing cell may not be identified as being fullyup-to-date, for example, when the writing cell is being taken offlineand replaced by another writing cell. Data may be transferred from theold writing cell to the new writing cell, and, during the transfer,updates to the instance that is assigned to the writing cell may bewritten to the new writing cell. Thus, neither of these cells may befully up to date and an indication of such a status may be stored withreference to one or both of these cells.

Stated another way, in some examples, the computing system can accessthe metadata for each of the blocks in the list and determine whethereach block is a most-recent version of the block. In some examples, themetadata for locally-stored blocks have flags that identify if theblocks are up to date. In some examples, the computing system cancompare metadata for each locally-stored block. The creation date ofeach locally-stored block can be compared to the creation date that isstored in the global table.

In box 2810, in response to identifying that at least two cells havefully replicated the blocks, the computing system identifies a cell tohandle the query based on a criterion. As a first example, the computingsystem may determine which of the at least two cells is geographicallyclosest to an estimated geographical position of the remote computingdevice (e.g., as determined using an IP address of the remote computingdevice or a GPS determination by the remote computing device). In otherwords, if the blocks at a local data center are up to date, thecomputing system requests that that the query be performed on the tableat the local data center.

As a second example, the computing system may determine which of the atleast two cells is temporally closest to remote computing device basedon a shortest trip time for a message transmitted between the remotecomputing device and each of the at least two cells. As an illustration,the remote computing device may PING each of the at least two cells, andthe cell associated with the shortest PING may be selected as the cellto which the query is sent.

As a third example, the computing system may determine which of the atleast two cells has been determined to have the most-available computingcapacity. For instance, the computing system may implement a loadbalancing procedure to identify which of the cells is underutilized. Thecomputing system may select the local cell by requesting that anexternal process identify, based on various metrics, a cell from acollection of cells. The metrics can include load balancing andestimated latency in providing the computing system of the external userwith a response.

In box 2816, the computing system sends the query to the identified datacenter, and, in box 2824, a local copy of the querying system (e.g.,querying subsystem 2532) executes the query on locally stored data(e.g., data cache 2534).

In box 2812, in response to identifying that none of the cells havefully replicated the blocks, the computing system identifies a cell thatmost-recently updated the blocks. As discussed, above, the computingsystem may determine that the storage entries for the blocks from whichdata is to be selected indicate that none of the cells are fullyup-to-date. This may be the result, for example, when the blocks arestored by an instance, and the writing cell for that instance is beingtransitioned to another writing cell. In this case, some of the one ormore blocks may be stored by the old writing cell and some of the one ormore blocks may be stored by the new writing cell. In such acircumstance, the computing system may send the query to the cell thatmost-recently updated the one or more blocks. The computing system maydetermine such a cell by identifying which of the cells has a mostrecent date cached timestamp for any of the one or more blocks.

In another illustration, because data can take time to replicate amongthe cells in a cohort, querying the most-recent writing cell can ensurethat the system is querying the most-recent version of the table. Statedanother way, the cell that most-recently wrote to the table may be thewriting cell for the instance that stores the table. The writing cellfor such an instance may have the most up to date information for thetable.

In box 2826, the most-recently updated cell may perform the query.

Performing the query at the most-recently updated cell may includerequesting that the most-recently updated cell copy any necessary blocksfrom the old writing cell so that the most-recently updated cell has afully updated set of blocks. Performing the query at the most-recentlyupdated cell may include requesting that the most-recently updated cellrequest partial performance of the query at the most-recently updatedcell (on the blocks stored at the most-recently updated cell), andpartial performance of the query at the old writing cell (on the blocksstored at the old writing cell).

In box 2822, in response to identifying that one of the cells has fullyreplicated the blocks, the computing system sends the query to the onecell. The one cell may then perform the query on locally-stored data.

The above description may refer to a read-only querying operation. Awriting query operation may determine the cell that is assigned as thewriting cell for the instance that includes the table data, and mayrequest that the writing cell perform the query, as described withrespect to FIGS. 30A-C.

The above description refers at times to data centers for illustrativepurposes, but it should be understood that querying operations acrossgeographically distributed data centers may use a single cell at each ofthe data centers. As such, the operations may be performed by a singlecell at each of the described data centers. For example, the table datamay be stored by a single cell at each of the data centers and the querymay ultimately be executed by the querying subsystem at a single cell.

FIG. 29 is a flowchart showing an example of a process for inboundreplication of data. The process of FIG. 29 may be performed by each ofmultiple cells identified in a cohort to ensure that that the data inthe each of the cells remains up to date. At any given time, each of theinstances in a cohort may be assigned to a cell. For example, in acohort with three cells and four instances, all cells may store all fourinstances, while one of the cells is the writing cell to four instancesand two of writing cells write to only a single instance. As such, it ispossible that a cell is assigned as the writing cell to multipleinstances or that a cell is not assigned as the writing cell to anyinstances.

As discussed above, each cell may include a local table that identifiesthe state of the data that is stored by all cells in a given cohort.This state information may replicate quickly among the cells, but theunderlying data that backs the user-generated tables may not be able toreplicate so quickly. As such, a single cell may handle the writingoperations for its assigned instance. That cell, however, also may storemultiple other instances for which it is not the writing cell. Thereplication process for these other instances is described below. Thisreplication process may be handled by a cache manager (e.g., cachemanager 2538).

In box 2902, the cache manager at a particular cell identifies a list ofinstances in a cohort to which the particular cell is assigned. In theexample of FIG. 24, the cache manager of Cell A 2440 may identify thatinstances 2410, 2420, and 2430 are assigned to cohort 2400 and thus arestored by Cell A 2440.

In box 2904, the cache manager may identify a writing cell for eachinstance. In the example of FIG. 24, the cache manager of Cell A 2440may identify that Cell A is the writing cell for instance 2410, thatCell D is the writing cell for instance 2420, and that cell G is thewriting cell for instance 2430.

In box 2906, the cache manager may select a remote instance. In otherwords, the cache manager may select one of the instances for which thecell that includes the cache manager is not a writing cell. In theexample of FIG. 25, the cache manager 2538 is executed by Cell A 2440which is the writing cell for instance 2410. As such, the cache manager2538 may select one of instances 2420 or 2430.

In box 2908, the cache manager determines if the selected instance, atits writing cell, is more up to date than the selected instance at thelocal cell. In the example of FIG. 25, the cache manager 2538 maydetermine if the instance 2420 that is stored at Cell D is more up todate than the instance 2420 that is stored at Cell A 2440. The cachemanager 2538 may perform this determination by comparing the “datecached” identifier stored for each file in the local table of Cell D2450 (e.g., Local Table D 2760) to the “date cached” identifier storedfor each file in the local table of Cell A 2440 (e.g., Local Table A2710).

Because Cell D 2450 may be the only writing cell for instance 2420, ifthe time cached between the two files is different and the most-recenttime is for the writing cell, the cache manager 2538 can determine thatinstance 2420 in Cell A may have to be updated with the information fromCell D 2450. If the writing cell for the selected instance is more up todate than the local instance, the operations of box 2910 are performed.

In box 2910, the cache manager copies one or more files from theselected instance at the writing cell to the selected instance at thelocal cell. As an example, each file that has a newer “date cached”identifier at the writing cell may be copied to the local cell. In someimplementations, the system may not update files, but may delete oldfiles and upload new files that include the updated information in orderto update information. In such an example, the local cell may deletefiles that are no longer stored at the writing cell and may add filesthat were newly added to the writing cell.

If the cache manager does not determine that the selected instance atthe writing cell is more up to date than the selected instance at thelocal cell, then the operations of box 2912 are performed.

In box 2912, the cache manager determines if all remote instances havebeen processed. If not, the cache manager selects (in box 2914) the nextremote instance from the identified list of instances and performs theoperations of box 2908. If all remote instances have been processed, thecache manager repeats the process by performing the operations of box2902.

FIGS. 30A-C show a swim-lane diagram illustrating an example of aprocess for writing and replicating data.

In box 3002, a computing system receives a first writing query. Forexample, the computing may receive from a remote computing device, afirst request to insert one or more first data values into a firstdatabase table. As an illustration, the frontend server 2126 (FIG. 21)may receive a query through the query API 2124 as having been sent froma remote computing device of a third-party organization. The receipt ofthe query “from” the remote computing device may indicate that the querywas received as having been sent from the remote computing device. Thequery may be transmitted through intervening computing systems.

In box 3004, the computing system identifies that data that isresponsive to the first query is stored by a first instance. Forexample, the frontend server 2126 may analyze the query to identify adatabase table to which the query is structured to write. The frontendserver 2126 may access the global table 2514 to retrieve informationthat indicates an instance in which the table is stored.

In box 3006, the computing system identifies that a first cell is awriting cell for the first instance. For example, the frontend server2126 may access the global table 2514 in order to identify which of themultiple cells, among which the instance is replicated, serves as thewriting cell for the instance. As described throughout this document alogical collection of data (referred to herein as a cohort) may bereplicated among multiple cells, but the writing responsibilities forportions of the logical collection of data may be divided among themultiple cells. For example, one cell may initially write to a firstpartition of the logical collection of data (referred to herein as aninstance) and another cell may initially write to a second partition ofthe logical collection of data.

The computing system may dispatch all queries that the computing systemreceives during a time period (e.g., 1 hour or 1 day), and that arestructured to write to the first instance, to the cell that is assignedto write to the first instance. The system may not dispatch such queriesto any other of the cells and any other of the cells may not bedesignated to write to the first instance during the time period. Atleast fifty queries, and maybe hundreds or thousands of queries, thatare structured to write to the first instance may be received during thetimer period from a variety of remote computing devices.

In box 3008, the computing system sends the query to the writing cell.For example, the computing system may send the first request to insertthe one or more first data values into the first database table forreceipt by computing devices at a particular data center that areassigned to the first cell. The sending of the query or the firstrequest may include sending a query or first request that has changed inform, but that includes instructions that are similar or identical tothe original query or the first request that was received from theremote computing device. The sending of the query “to” the writing cellmay indicate that the query was sent for receipt by the writing cell.The query may be transmitted through intervening computing systems.

In box 3010, the first cell of computing devices receives the firstquery. For example, the first datacenter may receive, from the computingsystem, the first query or the first request.

In box 3012, the first cell of computing devices inserts the dataidentified by the first writing query into the first database table. Forexample, the first cell may insert the one or more first data valuesinto a copy of the first database table that is stored by the first datacenter. As an illustration, multiple computing devices included in thefirst cell may generate new blocks of columnar data that add to the oldblocks of columnar data the values identified by the query. The multiplecomputing devices included in the first cell may replace old blocks ofcolumnar data with the newly-generated blocks of columnar data. The datavalues identified by the query may be included in the query or may beincluded in another database table that was identified by the query, forexample.

In box 3020, the first cell of computing devices replicates dataidentified by the first writing query to other cells. For example, thecache manager at the cell (e.g., cache manager 2538) may send theupdated one or more blocks of columnar data for receipt by each of theother cells among which the blocks of columnar data are replicated. Inanother example, the first cell sends the query for receipt by each ofthe other cells, and each of the other cells executes the query.

In box 3022, a second cell of computing devices receives the dataidentified by the first writing query. For example, the second cell mayreceive the updated one or more blocks of columnar data, or may receivethe query.

In box 3024, the second cell of computing devices inserts the dataidentified by the first writing query into the first database table. Forexample, the second cell of computing devices may insert the one or morefirst data values into a copy of the first database table that is storedby the second data center. The second cell of computing devices may dothis by replacing certain blocks of columnar data with the updatedblocks of columnar data that the second cell received from the firstcell. In such an example, the second cell of computing devices does notgenerate the updated blocks of columnar data and instead just receivessuch updated blocks of columnar data from the first cell. Alternatively,the second cell may execute the query that the second cell received fromthe first cell, and insert values identified by the query into the copyof the first database table that is stored by the second cell.

Boxes 3026 through 3044 illustrate a process of receiving a secondwriting query and writing data initially to the second cell of computingdevices, whereby the second cell of computing devices replicates thedata to the first cell of computing devices. The operations of boxes3026 through 3044 are similar to those of boxes 3002 through 3024,except that the second writing query is structured to write to a secondinstance that is assigned the second cell as a writing cell. Boxes3026-3044 have been included in the diagram of FIGS. 30A-C to illustratethat different queries are dispatched to different cells of computingdevices based on the logical storage location of the data to which thequeries are structured to write.

Although queries are dispatched to specific cells for initial writingsof the data to the database tables, each of the data centers mayeventually end up with an up-to-date set of data, such that all cellsamong which an instance and its parent cohort are replicated mayeventually store the same versions of one or more blocks of data.

Moreover, each of the cells among which the data is replicated may becapable of executing reading queries on the data. This is process isillustrated by boxes 3050 through 3058, which illustrate that a remotecomputing device's submission of a query that is structured to write toeither a first database table or a second database table may be receivedby either the first cell or the second cell, and the local queryingsubsystem (e.g., querying subsystem 2532) may execute the query. Thecell that executes a reading query may be selected as described by FIG.28.

In some implementations, all data that is stored by the first databasetable is stored within the first instance, and all data that is storedby the second database table is stored within the second instance. Forexample, all columnar data blocks that back a table may be stored withina same instance. Blocks that back a same table may not be split betweendifferent instances, in some implementations.

In some implementations the first cell is designated as an only cell ofthe multiple cells to write to a first instance of data concurrently asa second cell is designated as an only cell of the multiple cells towrite to a second instance of data. For example, during a same timeperiod, all queries that write to the first instance may be designatedfor routing to the first cell and all queries that write to the secondinstance may be designated for routing to the second cell.

The following portion of the disclosure describes some examples of datastructures that may be stored in the global table and examples of APImethod calls that the web service data processing system may support(and therefore receive).

Dataset Resource

A dataset can contain zero or more tables. There may not be anidentified limit to the number of tables or the total data size of adataset. Dataset access control lists (ACLs) can apply to datasets,tables, and table data. Jobs may be dependent on a mix of dataset andproject ACLs. The project owner may have delete rights to any datasetwithin the project, but may not be guaranteed any other rights to anydataset in that project.

The following illustrates an example data structure in JSON for adataset.

{  “kind”: “processingservice#dataset”,  “id”: string,  “selfLink”:string,  “projectId”: string,  “datasetId”: string, “friendlyName”:string,  “description”: string,  “access”: [   {  “role”: string,   “userByEmail”: string,   “groupByEmail”: string,  “domain”: string,   “allAuthenticatedUsers”: boolean  }  ], “creationTime”: long,  “lastModifiedTime”: long }

The following illustrates the property names for the dataset structure,a type of the value for the property, whether the property is mutable,and a description of the property.

The property “kind” can have a value “processingservice #dataset” andmay not be mutable. This property can be the resource type.

The property “id” can have a value “string” and may not be mutable. Thefully-qualified unique name of this dataset may be in the formatprojectId:datasetId. The dataset name without the project name may begiven in the datasetId field. When creating a new dataset, one may leavethis field blank, and instead specify the datasetId field.

The property “selfLink” can have a value “string” and may not bemutable. It may reference a URL that can be used to access this resourceagain. This URL can be used in Get or Update requests to this resource.

The property “project ID can have a value “string” and may be mutable oncreation. The property can be the ID of the container project. Thedefault may be the current project.

The property “datasetId” can have a value “string” and may be mutable oncreation. The property can be a unique ID for this dataset, without theproject name. This may be an optional field. If this is not specifiedwhen a dataset is created, an ID may be assigned for the dataset. Thedataset ID may be unique within the project. The dataset ID may be astring of 1-1024 characters satisfying the regular expression[A-Za-z0-9_]

The property “friendlyName” can have a value “string” and may bemutable, but requires owner rights. This property can be an optionaldescriptive name for this dataset, which may be shown in any dataprocessing service user interfaces for browsing the dataset. Theproperty datasetId, however, may be used for making API calls. Thedefault is an empty string.

The property “description” can have a value “string” and may be mutable,but requires owner rights. This property can be an optional arbitrarystring description for the dataset. This might be shown in the dataprocessing service user interface for browsing the dataset. The defaultis an empty string.

The property “access” can have a value “list” and may be mutable, butrequires owner rights. This property can be optional and may describeusers' rights on the dataset. The same role can be assigned to multipleusers, and multiple roles can be assigned to the same user. Defaultvalues assigned to a new dataset may be as follows: OWNER—Projectowners, dataset creator; READ—Project readers; and WRITE—Projectwriters. If any of these roles are specified when creating a dataset,the assigned roles may overwrite the defaults listed above. To revokerights to a dataset, a user can call datasets.update( ) and omit thenames of anyone whose rights should be revoked. However, every datasetmay have at least one entity granted OWNER role. Each access object mayhave only one of the following members: userByEmail, groupByEmail,domain, or allAuthenticatedUsers.

The property “access.role” can have a value “string” and may be mutable,but requires owner rights. This property describes the rights granted tothe user specified by the other member of the access object. Thefollowing string values are supported: READ—User can call any list( ) orget( ) method on any collection or resource. WRITE—User can call anymethod on any collection except for datasets, on which they can calllist( ) and get( ) OWNER—User can call any method. The dataset creatoris granted this role by default.

The property “access.userByEmail” can have a value “string” and may bemutable, but requires owner rights. This property describes a fullyqualified email address of a user to grant access to. For example:fred@example.com.

The property name “access.groupByEmail” can have a value “string” andmay be mutable, but requires owner rights. This property describes afully-qualified email address of a mailing list to grant access to.

The property “access.domain” can have a value “string” and may bemutable, but requires owner rights. This property describes a domain togrant access to. Any users signed in with the domain specified may begranted the specified access. Example: “example.com”.

The property “access.allAuthenticatedUsers” can have a value “boolean”and may be mutable, but requires owner rights. If True, anyauthenticated user may be granted the assigned role. The default isFalse. Be aware that all users everywhere may be granted the access.roleright to all tables in a database with this ACL.

The property “creationTime” can have a value “long” and may not bemutable. This property describes the date when this dataset was created,in milliseconds since the epoch.

The property “last ModifiedTime” can have a value “long” and may not bemutable. This property describes the date when this dataset or any ofits tables was last modified, in milliseconds since the epoch.

processingservice.datasets.list

This method can list all the datasets in the specified project to whichthe caller has read access; however, a project owner can list (but notnecessarily get) all datasets in his project.

Required ACLs: To call this method, the user may have to have one of thefollowing rights: project.READ/WRITE/OWNER may enable the user to listall datasets in the project. dataset.access.READ/WRITE/OWNER may returnany datasets that the user has explicit access to.

REST Request: GEThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets[?pageToken={page_token}]&[maxResults={max_results}]

The parameter “pageToken” can have the type “integer.” A page token canbe used when requesting a specific page in a set of paged results.

The parameter “maxResults” can have the type “integer.” This parametercan be the maximum number of rows to return. If not specified, it mayreturn up to the maximum amount of data that may fit in a reply.

Response:

{ “kind”: “processingservice#datasetList”, “etag”: string,“nextPageToken”: string, “datasets”: [  {  “id”: string,  “projectId”:string,  “datasetId”: string,  “friendlyName”: string  } ] }

The following illustrates the property names for the dataset structure,a type of the value for the property, and a description of the property.

The property “kind” can have the value “processingservice#datasetList.”The property can be the resource type.

The property “etag” can have the value “string.” The property can be ahash of the page of results.

The property “nextPageToken” can have the value “string.” The propertycan be a token to request the next page of results. It may be presentonly when there is more than one page of results.

The property “datasets” can have the value “list.” The property can bean array of one or more summarized dataset resources. The property maybe absent when there are no datasets in the specified project.

The property “datasets.id” can have the value “string.” The property canbe the fully-qualified unique name of this dataset in the formatprojectId:datasetId.

The property “datasets.projectId” can have the value “string.” Theproperty can be the ID of the container project.

The property “datasets.datasetId” can have the value “string.” Theproperty can be the unique ID for this dataset. This can be the ID valuewithout the project name.

The property “datasets.friendlyName” can have the value “string.” Theproperty can be a descriptive name for this dataset.

processingservice.datasets.get

This method can return the dataset specified by datasetID. The user mayspecify the unqualified datasetId value and not the fully-qualified idvalue of the dataset.

Required ACLs: To call this method, the user must have one of thefollowing rights dataset.access.READ; dataset.access.OWNER;project.READ; project.OWNER.

REST Request: GEThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets/{datasetId}

Response: The response returns a dataset resource or an error message.If the requested dataset does not exist, it returns an HTTP 404 error.

processingservice.datasets.insert

This method may create a new empty dataset.

Required ACLs: To call this method, the user may have one of thefollowing rights: Write or Owner rights on the containing project.

REST Request: POSThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets

The request passes in an object with the following members (see thedataset resource for further detail:

{  “projectId”: string, // Required  “datasetId”: string, // Required “friendlyName”:string, // Optional  “description”: string, // Optional “access”: [ // Optional   {   “role”: string, // Required. One of thefollowing four also   required.   “userByEmail”: string,  “groupByEmail”: string,   “domain”: string,   “allAuthenticatedUsers”:boolean  }  ] }

Response: The response may return a copy of the new dataset resource ifsuccessful, or an error message if not successful.

processingservice.datasets.update

This method can update information in an existing dataset, specified bydatasetId. Properties not included in the submitted resource may not bechanged. If a user includes a member without a value, it may be reset tonull. Note that if a user includes the access property without anyvalues assigned, the request may fail as the user may have to specify atleast one owner for a dataset.

WRITE access on the dataset to may be required to modify any memberexcept access. OWNER access on the dataset may be required to modify theaccess member. The specified access list may completely overwrite theexisting access list. If a user specifies an empty access list, accessmay be revoked to everyone except the user; the user cannot remove allowners from a dataset.

Required ACLs: To call this method, the user may have to have thefollowing rights: dataset.access.OWNER.

REST REQUEST: PUThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets/{datasetId}

Request data: The user can pass in the following data object with anyvalues that the user wishes to modify. The user can omit any propertiesthat the user wishes do not wish to change.

  {  “friendlyName”:string, // Optional  “description”: string, //Optional  “access”: [ // Optional   {   “role”: string, // Required. Oneof the following four also   required.   “userByEmail”: string,  “groupByEmail”: string,   “domain”: string,   “allAuthenticatedUsers”:boolean  }  ] }

Response: Returns a copy of the updated dataset resource if successful,or an error message if not.

processinqservice.datasets.delete

This method deletes the dataset specified by datasetId value. Before auser can delete a dataset, the user may have to delete all its tables,either manually or by specifying deleteContents. Immediately afterdeletion, the user can create another dataset with the same name.

Required ACLs: To call this method, the user must have one of thefollowing rights: dataset.access.OWNER; project.OWNER.

REST Request: DELETEhttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets/{datasetId}[?deleteContents={delete_contents}]

The parameter “deleteContents” can have the type “Boolean.” Theparameter is optional and, if true, deletes all the tables in thedataset. If False and the dataset contains tables, the request may fail.The default is False.

Response: The method returns an HTTP 202 (not found) response ifsuccessful, or an error message.

Job Resource

A job is an operation performed at the request of a user on a dataset.Jobs include SQL queries, table import requests, and table exportrequests, for example. The resource contains a different configurationmember depending on what kind of job this is. The JSON data structurefor a job follows:

  { “kind”: “processingservice#job”, “id”: string, “selfLink”: string,“projectId”: string, “jobId”: string, “configuration”: {  “query”: { //Run query, or   “query”: string,   “destinationTable”: {    “projectId”:string,    “datasetId”: string,    “tableId”: string   },  “createDisposition”: string,   “writeDisposition”: string,  “defaultDataset”: {    “datasetId”: string,    “projectId”: string   } },  “load”: { // Import data into a table   “sourceUris”: [    string,   ...   ],   “schema”: {    “fields”: [    {     “name”: string,    “type”: string,     “mode”: string,     “fields”: [     {...optional nested “schema” objects ...}     ]    }   ]  }, “destinationTable”: {   “projectId”: string,   “datasetId”: string,  “tableId”: string  },  “createDisposition”: string, “writeDisposition”: string }, “extract”: { // Export to Company Storage “sourceTable”: {   “projectId”: string,   “datasetId”: string,  “tableId”: string  },  “destinationUri”: string } }, “status”: { “state”: string,  “errorResult”: {   “domain”: string,   “code”:string,   “errorMessage”: string  },  “errors”: [   {   “domain”:string,   “code”: string,   “errorMessage”: string   }  ] },“statistics”: {  “startTime”: long,  “endTime”: long } }

The property “kind” can have a value “processingservice#job” and may notbe mutable. The property can be the resource type.

The property “id” can have a value “string” and may not be mutable. Theproperty can be the fully-qualified job name, in the formatprojectId:jobId. When creating a new job, the user may not specify thisvalue, and may instead specify the jobId field. The unqualified job IDcan be retrieved from the jobId field.

The property “selfLink” can have a value “string” and may not bemutable. The property can be an URL that can be used to access thisresource again. The user can use this URL in get( ) requests for thisresource.

The property “projectId” may have a value “string” and may not bemutable. The property is the ID of the project that contains this job.This is the project that may be billed for the job.

The property “jobId” may have a value “object” and may be mutable atcreation. The property may reference a unique ID for this job within theproject. This value may be optional when the user creates a new job. Ifthe user does not specify a value here, a random ID may be chosen. Theproperty may have to be a string of 1-1024 characters satisfying theregular expression [A-Za-z0-9_\-].

The property “configuration” may have a value “object” and may bemutable at creation. The property may reference an object that specifiesthe details of this job. When inserting a new job, the user may includeonly one of the following child objects, depending on what type of jobit is. load—Create and populate a new table from a CSV file. query—Run aquery. extract—Export a Processingservice table to Company Storage as aCSV file.

The property “configuration.query” may have a value “object” and may bemutable at creation. The property can be an object that must be presentonly when sending a query. If the query returns one or more result rows,results may be stored in the table specified by the destination Tableproperty.

The property “configuration.query.query” may have a value “string” andmay be mutable at creation. The property can be a query string,following the Processingservice query syntax of the query to execute.Table names may have to be qualified by dataset name in the formatprojectId:datasetId.tableId unless the user specifies the defaultDatasetvalue. If the table is in the same project as the job, the user can omitthe project ID. Example: SELECT f1 FROMmyprojectId:myDatasetId.myTableId.

The property “configuration.query.destinationTable” may have a value“object” and may be mutable at creation. The property can be an optionalobject describing a destination table where results may be saved. Theuser may specify whether to create a new or overwrite an existing tableby specifying createDispensation and writeDispensation.

The property “configuration.query.destinationTable.projectId” may have avalue “string” and may be mutable at creation. The property can be theproject ID of the dataset where the table should be created.

The property “configuration.query.destinationTable.datasetId” may have avalue “string” and may be mutable at creation. The property can be thedatasetId of the dataset in which to create the result table. This maynot be qualified by the project ID. The user may have to have writeaccess on this dataset. If the specified dataset does not exist, themethod may return an error.

The property “configuration.query.destinationTable.tableId” may have avalue “string” and may be mutable at creation. The property mayreference the tableId of the table to hold the results. This can be anexisting table or not, depending on the query. createDisposition value.

The property “configuration.query.createDisposition” may have a value“string” and may be mutable at creation. This property may be optionaland can be whether to create a results table if no table by that IDalready exists. The following string values may be supported:CREATE_NEVER—[Default] Do not create a new table to hold results.CREATE_IF_NEEDED—Create a new table if one does not exist and the queryresult set has more than zero records.

The property “configuration.query.writeDisposition” may have a value“string” and may be mutable at creation. The property may be optionaland may reference whether or not to overwrite an existing results tablewith the specified name. The following string values are supported:WRITE_EMPTY—[Default] Only write to a table with no data, otherwisefail. WRITE_TRUNCATE—Clear the data from the existing table and appendthe output data. The table schema must match the schema of the new data.WRITE_APPEND—Append the new data to any existing data. The table schemamay have to match the schema of the new data.

The property “configuration.query.defaultDataset” may have a value“object” and may be mutable at creation. The property may specify thedefault datasetId and projectId to assume for any unqualified tablenames in the query. If not set, all table names in the query string maybe fully-qualified in the format projectId:datasetId.tableid. The usermay have to specify either both datasetId and projectId, or neither(omit this object).

The property “configuration.query.defaultDataset.datasetId” may have avalue “string” and may be mutable at creation. The property mayreference the assumed dataset ID of any tables that are not qualified bydataset.

The property “configuration.query.defaultDataset.projectId” may have avalue “string” and may be mutable at creation. The property mayreference the assumed project ID of any tables not qualified by project.

The property “configuration.load” may have a value “object” and may bemutable at creation. The property may reference an object that must bepresent only when importing data into a new or existing table from a CSVdata file.

The property “configuration.load.sourceUris” may have a value “list” andmay be mutable at creation. The property may reference a list of one ormore Company Storage objects containing table data. The device for theuser calling jobs.insert may have to have read access to all objectsreferenced. All objects may have to be CSV files in the proper format,with the same table schema. These may have to be be fully-qualifiednames, for example: gs://mybucket/myobject.csv

The property “configuration.load.schema” may have a value “object” andmay be mutable at creation. The property may reference a schemadescriptor that describes all imported tables. All tables may follow theschema described in this object.

The property “configuration.load.schema.fields” may have a value “list”and may be mutable at creation. The property may reference a list of oneor more objects, each describing a column in the imported table(s)schema. These values may be applied to the table created, if a new tableis created rather than appended to.

The property “configuration.load.schema.fields.name” may have a value“string” and may be mutable at creation. The property may reference afriendly name for this column. This name may be assigned to the columnin the newly created table.

The property “configuration.load.schema.fields.type” may have a value“string” and may be mutable at creation. The property may reference thefield type.

The property “configuration.load.schema.fields.mode” may have a value“string” and may be mutable at creation. The property may reference thefield mode.

The property “configuration.load.schema.fields.fields” may have a value“list of schema objects” and may be mutable at creation. The propertymay be present only in a column that holds nested fields. This objectmay describe any nested fields.

The property “configuration.load.destinationTable” may have a value“object” and may be mutable at creation. This object may be the tableIDof the destination table to hold the query results, if more than zeroresult rows are returned. The user may specify whether to create a newtable or overwrite or append to an existing one by settingcreateDispostion and writeDispostion.

The property “configuration.load.destinationTable.projectId” may have avalue “string” and may be mutable at creation. The property mayreference the assumed project ID of any tables not qualified by project.Optionally, the property may reference the ID of the project thatcontains the dataset to write the result table to. If not specified, thesystem may write to the project containing the job.

The property “configuration.load.destinationTable.datasetId” may have avalue “string” and may be mutable at creation. The property mayreference the unqualified ID of the dataset to write the result tableto. The user may have to have write access in this dataset.

The property “configuration.load.destinationTable.tableId” may have avalue “string” and may be mutable at creation. The property mayreference the unqualified table ID of the table to create or append to.The table may have to already exist unlessconfiguration.load.createDisposition=CREATE_IF_NEEDED.

The property “configuration.load.createDisposition” may have a value“string” and may be mutable at creation. The property may referencewhether or not to create a new table, if none exists. The followingstring values may be supported: CREATE_NEVER [Default]—Do not create anew table. CREATE_IF_NEEDED—If a table does not exist, create one.

The property “configuration.load.writeDisposition” may have a value“string” and may be mutable at creation. The property may referencewhether or not to overwrite an existing table. The following stringvalues may be supported: WRITE_EMPTY—[Default] Only write to a tablewith no data, otherwise fail. WRITE_TRUNCATE—If a table exists, replaceexisting data with new data. WRITE_APPEND—If a table exists, append newdata to the end.

The property “configuration.extract” may have a value “object” and maybe mutable at creation. The property may reference an object that may bepresent only when exporting a table to Company storage.

The property “configuration.extract.sourceTable” may have a value“object” and may be mutable at creation. The property may reference anobject describing the Processingservice table to be exported.

The property “configuration.extract.sourceTable.projectId” may have avalue “string” and may be mutable at creation. The property mayreference the ID of the project containing the source table.

The property “configuration.extract.sourceTable.datasetId” may have avalue “string” and may be mutable at creation. The property mayreference the datasetId of the dataset containing the source table. Theuser must have read access on this dataset.

The property “configuration.extract.sourceTable.tableId” may have avalue “string” and may be mutable at creation. The property mayreference the tableId of the table to export, in the specified dataset.

The property “configuration.extract.destinationUri” may have a value“string” and may be mutable at creation. The property may reference thefully-qualified Company Storage URI of the object where the user hasrequested that the table is saved. This is in the format bucket/objectwith the gs:// prefix. The user may have write access on this bucket.Example: gs://mybucket/myobject.

The property “status” may have a value “object” and may be mutable atcreation. The property may reference the status of this job. The usercan examine this value when polling an asynchronous job to see if thejob is complete.

The property “status.state” may have a value “string” and may be mutableat creation. The property may reference the current state of this job.The property supports the following string values: PENDING—Queued.RUNNING—Running. DONE—Completed, either successfully or not. Ifunsuccessful, the errorResult field should contain additionalinformation.

The property “status.errorResult” may have a value “object” and may notbe mutable at creation. The property may reference an object that mayonly be present if the job has failed.

The property “status.errorResult.domain” may have a value “string” andmay not be mutable at creation. The property may reference a scopingmechanism that, when combined with status.errors.reason, defines aunique string.

The property “status.errorResult.code” may have a value “string” and maynot be mutable at creation. The property may reference aProcessingservice error code appropriate for this error.

The property “status.errorResult.errorMessage” may have a value “string”and may not be mutable at creation. The property may reference auser-friendly description of the error. Status.errors.code may be usedto trap specific errors.

The property “status.errors” may have a value “list” and may not bemutable at creation. The property may reference a list of non-fatalerrors that occurred during job processing. It is possible that a jobcan complete successfully even if there are some errors. In import orexport requests, it is possible that some rows were unable to beimported or exported due to errors.

The property “status.errors.domain” may have a value “string” and maynot be mutable at creation. A scoping mechanism that, when combined withstatus.errors.reason, defines a unique string.

The property “status.errors.code” may have a value “string” and may notbe mutable at creation. The property may reference a Processingserviceerror code appropriate for this error.

The property “status.errors.debugInfo” may have a value “string” and maynot be mutable at creation. The property may reference additionalinformation about the error.

The property “status.errors.errorMessage” may have a value “string” andmay not be mutable at creation. The property may reference auser-friendly description of the error. Use status.errors.code to trapspecific errors.

The property “statistics” may have a value “object” and may not bemutable at creation. The property may reference information about thisjob.

The property “statistics.startTime” may have a value “long” and may notbe mutable at creation. The property may reference the start time ofthis job, in milliseconds since the epoch. This starts ticking when thejob status changes to RUNNING, not when the request is sent by the useror received by Processingservice.

The property “statistics.endTime” may have a value “long” and may not bemutable at creation. This may be end time of the job, in millisecondssince the epoch. This may be when the status changed to DONE,successfully or not. If the job has not finished, this member may not bepresent.

processingservice.jobs.list

This method may list all the Jobs in the current project that the userhas READ access to. Jobs may be retained indefinitely unless the usercalls processingservice.jobs.delete on a job. Authentication may berequired. To call the method, a user may need to have one of thefollowing rights: project.READ; Users who create a specific job can listthat job.

REST Request: GEThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/jobs[?pageToken={page_token}]&[maxResults={max_results}]

The parameter “page Token” can have a type of “Integer.” The parameteris optional and may reference a page token used when requesting a specifpage in a set of paged results.

The parameter “maxResults” can have a type of “Integer.” The parameteris optional and may reference the maximum number of rows to return. Ifnot specified, the method may return up to the maximum amount of datathat may fit in a reply.

The structure of the response follows:

  { “kind”: “processingservice#jobList”, “etag”: string,“nextPageToken”: string, “jobs”: [  {  “id”: string,  “projectId”:string,  “jobId”: string,  “state”: string,  “startTime”: long, “endTime”: long  }, ... additional jobs... ] }

The property “kind” can have a value “processingservice#jobList.” Theproperty can reference the resource type of the response.

The property “etag” can have a value “string.” The property canreference a hash of this page of results.

The property “nextPageToken” can have a value “string.” The property canreference a token to request the next page of results. The property maybe present only when there is more than one page of results.

The property “jobs” can have a value “list.” The property may referencean array of one or more Job descriptions. The property may be absentwhen there are no jobs in the specified project.

The property “jobs.id” can have a value “string.” The property mayreference the fully-qualified job ID, in the format projectId:jobId.

The property “jobs.projectId” can have a value “string.” The propertymay reference the id of the project that contains this job.

The property “jobs.jobId” can have a value “string.” The property mayreference the jobId of the job.

The property “jobs.state” can have a value “string.” The property mayreference the current state of this job.

The property “jobs.startTime” can have a value “long.” The property mayreference the start time of this job, in milliseconds since the epoch.The clock starts ticking when the job status changes to RUNNING, notwhen the request is sent by the user or received by the processingservice.

The property “jobs.endTime” can have a value “long.” The propertyreference the end time of this job, in milliseconds since the epoch. Theproperty is when the status changed to DONE, successfully or not. If thejob has not finished, this may be null.

The property “jobs.errorResult” can have a value “object.” An objectthat may be present only if a job has failed.

processinqservice.jobs.qet

This method retrieves the specified job by ID. The user may specify theunqualified jobId value, not the fully-qualified id value of the job. Tocall this method, the user may have to have one of the following rights:dataset.access.READ; dataset.access.WRITE; dataset.access.OWNER;project.READ. A user may also get any job that that user had created.

REST Request: GEThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/jobs/{jobId}

Response: The response can be a job resource or an error message. If therequested job does not exist, the system may return an HTTP 404 error.

processingservice.jobs.insert

This method starts a new asynchronous job. The user may have to havewrite access to a project in order to run a job. This method returnsimmediately. The user may have to call jobs.get( ) and examine the jobstatus to learn when the job is complete. The user may have to includeone and only one of the following child members in the job resource. Thechild member that that the user includes defines the type of job thatthis is.

The child member “load” may load data from a CSV file into a table. Thejob can create a new table, overwrite an existing table, or append datato an existing table with a matching schema, as the user specifies inthe job description. The caller may need to have read rights on anyobjects holding the data to import.

The child member “query” may query.

The child member “extract” may export a data processing service table toCompany Storage as a CSV file, using the data processing service CSVsyntax.

REST Request: POSThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/jobs

Request Data: The user can pass in the following object with appropriatevalues. The job resource documentation can provide additionalinformation.

  { “projectId”: string, // Required “jobId”: string, // Optional“configuration”: { // Required: one of the following members:  “query”:{ // Required for query jobs   “query”: string, // Required   “destinationTable”: { // Required     “projectId”: string, //Required     “datasetId”: string, // Required     “tableId”: string //Required    },    “createDisposition”: string, // Optional   “writeDisposition”: string, // Optional    “defaultDataset”: { //Optional     “datasetId”: string, // Optional     “projectId”: string //Optional    }   },   “load”: { // Required for import jobs   “sourceUris”: [ // Required one or more values     string,     ...   ],    “schema”: { // Required     “fields”: [     {     “name”:string, // Required     “type”: string, // Required     “mode”: string,// Required    }   ]  },  “destinationTable”: { // Required  “projectId”: string, // Required   “datasetId”: string, // Required  “tableId”: string // Required  },  “createDisposition”: string, //Optional  “writeDisposition”: string // Optional }, “extract”: { //Required for export jobs  “sourceTable”: { // Required   “projectId”:string, // Required   “datasetId”: string, // Required   “tableId”:string // Required  },  “destinationUri”: string // Required } } }

Response: The method returns a copy of the request data if successful.The user may have to call processingservice.jobs.get( ) with thereturned jobid value to poll the job status.

processinqservice.jobs.delete

The method deletes a completed job specified by jobId. The job may haveto be completed, either successfully or not, to call this method.

Required ACLs: To call this method, the user may have to have one of thefollowing rights: project.WRITE/OWNER; or be the job creator

REST Request: DELETEhttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/jobs/{jobId}

Response: HTTP 204 code on success

processinqservice.jobs.query

The method runs a synchronous SQL query. To perform an asynchronousquery, the user may call Jobs.insert( ). This method creates a resulttable, or deposits data in the specified table, if there are anyresults, and returns the first page of results synchronously. Aftergetting the first page of results, the user may have to use theprocessingservice.tabledata.list( ) command to page through anyadditional results in the result table specified by tableId. The numberof rows returned is limited by the lesser of either the maximum pagesize or the maxResults property. The create/write disposition valuesused by this method are CREATE_IF_NEEDED and WRITE_EMPTY.

Required ACLs: To call this method, the user may have to have one of thefollowing sets of rights: dataset.access.WRITE/OWNER+project.WRITE/OWNER

REST Request: POSThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/queries

Request data: An object with the following syntax.

  {  “query” : string, // Required  “destinationTable”: { // Required  “projectId”: string, // Required   “datasetId”: string, // Required  “tableId”: string // Required  },  “maxResults” : long, // Optional “defaultDataset” : { // Optional   “datasetId” : string, // Optional  “projectId” : string, // Optional  }  “createDisposition” : string, //Optional. Default is CREATE_IF_NEEDED  “writeDisposition” : string //Optional. Default is WRITE_EMPTY }

The property “maxResults” can have a value “long.” The property isoptional and can reference the maximum number of results to return perpage of results. If the response list exceeds the maximum response sizefor a particular response, the user may have to page through theresults. The default may be to return the maximum response size.

Response:

  {  “kind”: “processingservice#queryResults”,  “schema”: {   “fields”:[    {    “name”: string,    “type”: string,    “mode”: string,   “fields”: [    (nested field object...)    ]   }   ]  },  “job”:{  “kind” : “processingservice#job”,   “id” :string,   “selfLink”:string,   “projectId” :string,   “jobId” :string,   “configuration” :{job.configuration.query object},   “status” : {job.status object},  “statistics” : {job.statistics object},  }, “totalRows”: integer,“rows”: [  {   “f”: [    {    “v”: (value)   }   ]  }  ] }

The property name “kind” can have a value of“processingservice#queryResults.” The property can reference theresource type of the response.

The property name “id” can have a value of “string.” The property canreference the fully-qualified job name, in the format projectId:jobId.

The property name “selfLink” can have a value of “string.” The propertycan reference an URL that can be used to access this resource again. Theuser can use this URL in Get( ) requests for this resource.

The property name “projectId” can have a value of “string.” The user canuse the ID of the project that contains this job.

The property name “jobId” can have a value of “string.” The property canreference a unique ID for this job within the project.

The property name “configuration” can have a value of “object.” Theproperty can reference information about the query.

The property name “status” can have a value of “object.” The propertycan reference the status of this job. The user can examine this valuewhen polling an asynchronous job to see if the job is complete.

The property name “statistics” can have a value of “object.” Theproperty can reference statistics about this job.

The property name “schema” can have a value of “object.” The propertycan reference an object describing the schema of the result set.

The property name “job” can have a value of “object.” The property canreference a job resource describing the query job.

The property name “totalRows” can have a value of “integer.” Theproperty can reference the total number of rows in the complete queryresult set, which can be more than the number of rows in this singlepage of results.

The property name “rows” can have a value of “list.” The property canreference an object with as many results as can be contained within themaximum permitted reply size. To get any additional rows, the user maycall processingservice.tabledata.list( ).

The property name “rows.f” can have a value of “list.” The property mayrepresent a single row in the result set, consisting of one or morefields.

The property name “rows.f.v” can have any value. The property maycontains the field value in this row.

Project Collection

Project

A user can list projects to which the user has read access using theprocessingservice.projects.list method; however, to create or manageprojects, the user may have to use a separate system. Note that theproject name and the project ID are not the same thing: a project namemay be a human-readable string, and may not be required to be unique. Aproject ID may be a unique GUID string across all projects registered bythe system.

A project can hold zero or more datasets. When the user creates adataset, the user may have to assign it to an existing project. The usermay have at least have read access to a project to be able to see any ofthe datasets that it contains.

processingservice.projects.list

Lists all the projects to which a user has at least read access.

REST Request: GEThttps://www.companyapis.com/processingservice/v2beta1/projects[?pageToken={page_token}]&[maxResults={max_results}]

The parameter “pageToken” can have the type “Integer.” The parameter maybe optional and may reference a page token used when requesting aspecific page in a set of paged results.

The parameter “maxResults” can have the type “Integer.” The parametermay be optional and may reference the maximum number of rows to return.If not specified, the method may return up to the maximum amount of datathat may fit in a reply.

Response:

  {  “kind” : “processingservice#projectList”,  “etag” : string, “nextPageToken” : string,  “projects” : [   {   “id” : string,  “friendlyName” : string,  }  ] }

The property “kind” can have the value “processingservice#datasetList.”The property can reference the resource type.

The property “etag” can have the value “string.” The property canreference a hash of this page of results.

The property “nextPageToken” can have the value “string.” The propertycan reference a token to request the next page of results. The propertymay be present only when there is more than one page of results.

The property “projects” can have the value “list.” The property canreference an array of zero or more projects to which the user hasread/write access.

The property “proejcts.id” can have the value “string.” The property canreference the ID of this project.

The property “projects.friendlyName” can have the value “string.” Theproperty can reference a descriptive name for this project.

Tabledata Collection

Tabledata.

Tabledata is used to return a slice of rows from a specified table. Allcolumns may be returned. Each row is actually a Tabledata resource. Toget a slice of rows, the user may call processingservice.tabledata.list(). The user may specify a zero-based start row and a number of rows toretrieve in the list( ) request. Results may be paged if the number ofrows exceeds the maximum for a single page of data. The column order maybe the order specified by the table schema.

Tabledata Resource.

A JSON representation of a single row of table data. All columnsspecified by the table schema may be retrieved. See tabledata.list( )for details about how the returned data is structured.

processingservice.tabledata.list

This method may be used to retrieve table data from a specified set ofrows. The user may specify a zero-based starting row. If the userrequests a row index greater than the number of available rows, themethod may return successfully, but without a “rows” member. Columnlabels, table schema, and other metadata are may not be part of theresponse. Note that the response also may not include nextPageToken oretag members. Data may be returned in a JSON object that can be accessedin JavaScript, where the cell data is stored in the ‘v’ member of a twodimensional array, accessible like this:

-   -   cellVal=returnedJson[row][column].v;

Required ACLs: To call this method, the user may have to have one of thefollowing rights: dataset.access.READ; dataset.access.WRITE;

-   -   dataset.access.OWN ER.

REST Request: GEThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets/{datasetId}/tables/{tableId}/data[?startIndex={start_index}]&[maxResults={max_results}]

The parameter “startIndex” can have the type “Integer.” The parameter isoptional and may reference the zero-based index of the starting row.This can be useful to jump to the middle or end of a large table. Valuesgreater than the number of available rows may return successfully, butwithout a “rows” member.

The parameter “maxResults” can have the type “Integer.” The parameter isoptional and may reference the maximum number of rows to return. If notspecified, the method may return up to the maximum amount of data thatmay fit in a reply.

Response:

  {  “kind”: “processingservice#tableDataList”,  “totalRows”: integer, “rows”: [ // Present only when the result has data.   {   “f”: [    {   “v”: (value)   }   ]  }  ] }

The property “kind” can have a value “processingservice#tableDataList.”The property can reference the resource type of the response.

The property “total Rows” can have a value “integer.” The property canreference the total number of rows in the complete table, which can bemore than the number of rows in this single page of results.

The property “rows” can have a value “list.” The property can referencean array of one or more table rows. If the user requests a table with nodata, or with a start index beyond the table length, this member may beabsent.

The property “rows.f” can have a value “list.” The property canreference an array of cells in the row.

The property “rows.f.v” can have any value. The property can referencethe value of a single cell in the row.

Tables Collection

All tables are part of a dataset.

Tables Resource.

A table's schema is specified when the table is crated.

  {   “kind”: “processingservice#table”,   “id”: string,   “selfLink”:string,   “projectId”: string,   “datasetId”: string,   “tableId”:string,   “friendlyName”: string,   “description”: string,   “schema”: {   “fields”: [        {        “name”: string,        “type”: string,       “mode”: string,        “fields”: [        (nested or repeatedfields)       ]       }       ]      },      “creationTime”: string,     “lastModifiedTime”: long }

The property “kind” can have the value “processingservice#table,” andmay not be mutable. The property can reference the resource type ID.

The property “id” can have the value “string,” and may not be mutable.The property can represent the fully-qualified name of this table in theformat proejctId:datasetId:tableId.

The property “selfLink” can have the value “string,” and may not bemutable. The property can reference a URL that can be used to accessthis resource. This URL can be used in the get( ) or update( ) requeststo this resource.

The property “projectId” can have the value “string,” and may be mutableon creation. The property can reference the projectId of the projectthat contains this table.

The property “datasetId” can have the value “string,” and may be mutableon creation. The property can reference the datasetId of the datasetthat contains this table.

The property “tableId” can have the value “string,” and may mutable oncreation. The property can reference a unique ID for this table. Usethis to refer to the table in code. The user may have to specify thisvalue when creating a new table. This property may have to be a stringof 1-1024 characters satisfying the regular expression [A-Za-z0-9\-].

The property “friendlyName” can have the value “string,” and may bemutable on creation. The property can reference an optionaluser-friendly name for this table.

The property “description” can have the value “string,” and may bemutable. The property can reference a user-friendly description of thistable.

The property “schema” can have the value “object,” and may be mutable oncreation. The property can reference an object describing the schema ofthis table.

The property “schema.fields” can have the value “list,” and may bemutable on creation. The property can reference an array of itemsdescribing this field.

The property “schema.fields.name” can have the value “string,” and maybe mutable on creation. The property can reference the name of thisfield.

The property “schema.fields.type” can have the value “string,” and maybe mutable on creation. The property can reference the data type of thisfield.

The property “schema.fields.mode” can have the value “string,” and maybe mutable on creation. The property can reference the mode of thisfield (whether it can be null or not).

The property “schema.fields.fields” can have the value “list,” and maybe mutable on creation. The property can be used for describing nestedfields in a table.

The property “creationTime” can have the value “long,” and may not bemutable. The property can reference the time when this table wascreated, in milliseconds since the epoch.

The property “lastModifiedTime” can have the value “long,” and may notbe mutable. The property can reference the time when either the tableschema or table data was last modified, in seconds since the epoch.

processinqservice.tables.list

This method lists all tables in the specified dataset.

Required ACLs: To call this method, a user may have to have one of thefollowing rights: dataset.access.READ; dataset.access.WRITE;dataset.access.OWN ER.

REST Request: GEThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets/{datasetId}/tables[?pageToken={page_token}]&[maxResults={max_results}]

The parameter “pageToken” can have the type “Integer.” The parameter maybe optional and may reference a page token used when requesting aspecific page in a set of paged results.

The parameter “maxResults” can have the type “Integer.” The parametermay be optional and may reference the maximum number of rows to return.If not specified, the method may return up to the maximum amount of datathat may fit in a reply.

Response:

  {  “kind”: “processingservice#tableList”,  “etag”: string, “nextPageToken”: string,  “tables”: [   {   “id”: string,  “projectId”: string,   “datasetId”: string,   “tableId”string,  “friendlyName”: string  }, ...  ],  “totalItems”: integer }

The property “kind” can have the value “string.” The property referencesthe resource type of the response.

The property “etag” can have the value “string.” The property referencesa hash of this page of results.

The property “nextPageToken” can have the value “string.” The propertyreferences a token to request the next page of results. The property maybe present only when there is more than one page of results.

The property “tables” can have the value “list.” The property referencesan array of descriptions of one or more tables. The property may beabsent when there are no tables in the specified dataset.

The property “tables.id” can have the value “string.” The propertyreferences the fully-qualified name of this table in the formatprojectId:datasetId.tableId.

The property “tables.projectId” can have the value “string.” Theproperty references the projectId of the project that contains thistable.

The property “tables.datasetId” can have the value “string.” Theproperty references the datasetId of the dataset that contains thistable.

The property “tables.tableId” can have the value “string.” The propertyreferences the unique ID for this table.

The property “tables.friendlyName” can have the value “string.” Theproperty references the user-friendly name of this table.

The property “totalltems” can have the value “integer.” The propertyreferences the total number of tables in the dataset.

processingservice.tables.get

This method gets the specified table resource by tableId. This methodmay not return the data in the table, only the table resource, whichdescribes the structure of this table. To retrieve table data, a usercan call tabledata.list( ). The user should specify the unqualifiedtableId value, not the fully-qualified id value of the table.

Required ACLs: To call this method, the user may have to have one of thefollowing rights: dataset.access.READ; dataset.access.WRITE;dataset.access.OWN ER.

REST Request: GEThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets/{datasetId}/tables/{tableId}

Response: Returns the specified table resource, or an error message. Ifthe requested table does not exist, returns an HTTP 404 error.

processingservice.tables.insert

The method creates a new, empty table in the dataset, with the schemaprovided. Once a table is created using this method, the user may not beable to modify the schema, even if the table does not yet hold data. Topopulate a table with data, a user can create an appropriate job.

Required ACLs. To call this method, the user may have to have one of thefollowing rights: dataset.access.WRITE; dataset.access.OWNER.

REST Request: POSThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets/{datasetId}/tables

Request data: Submit with an object of the following syntax.

  {  “projectId”: string, // Required  “datasetId”: string, // Required “tableId”: string, // Required  “friendlyName”: string, // Optional “description”: string, // Optional  “schema”: { // Required   “fields”:[ // Required one per column    {    “name”: string, // Required   “type”: string, // Required    “mode”: string, // Required   }   ]  }}

Response: Returns a copy of the inserted table resource, or an errormessage.

processingservice.tables.update

The method updates information in an existing table, specified bytableId.

Required ACLs. To call this method, the user may have to have one of thefollowing rights: dataset.access.WRITE; dataset.access.OWNER.

REST Request: PUThttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets/{datasetId}/tables/{tableId}

Request data: submit an object with the following properties as desired.Properties not included in the submitted resource may not be changed.

  {  “friendlyName”: string, // Optional  “description”: string, //Optional }

Response: Returns a copy of the updated table resource if successful, oran error message.

processingservice.tables.delete

Deletes the table specified by tableId from the dataset. If the tablecontains data, all the data may be deleted. After deletion, the user maybe able to immediately create a new table with the same name.

Required ACLs. To call this method, the user may have to have one of thefollowing rights: dataset.access.WRITE; dataset.access.OWNER.

REST Request: DELETEhttps://www.companyapis.com/processingservice/v2beta1/projects/{projectId}/datasets/{datasetId}/tables/{tableId}

FIG. 31 is a block diagram of computing devices 3100, 3150 that may beused to implement the systems and methods described in this document, aseither a client or as a server or plurality of servers. Computing device3100 is intended to represent various forms of digital computers, suchas laptops, desktops, workstations, personal digital assistants,servers, blade servers, mainframes, and other appropriate computers.Computing device 3150 is intended to represent various forms of mobiledevices, such as personal digital assistants, cellular telephones,smartphones, and other similar computing devices. Additionally computingdevice 3100 or 3150 can include Universal Serial Bus (USB) flash drives.The USB flash drives may store operating systems and other applications.The USB flash drives can include input/output components, such as awireless transmitter or USB connector that may be inserted into a USBport of another computing device. The components shown here, theirconnections and relationships, and their functions, are meant to beexemplary only, and are not meant to limit implementations describedand/or claimed in this document.

Computing device 3100 includes a processor 3102, memory 3104, a storagedevice 3106, a high-speed interface 3108 connecting to memory 3104 andhigh-speed expansion ports 3110, and a low speed interface 3112connecting to low speed bus 3114 and storage device 3106. Each of thecomponents 3102, 3104, 3106, 3108, 3110, and 3112, are interconnectedusing various busses, and may be mounted on a common motherboard or inother manners as appropriate. The processor 3102 can processinstructions for execution within the computing device 3100, includinginstructions stored in the memory 3104 or on the storage device 3106 todisplay graphical information for a GUI on an external input/outputdevice, such as display 3116 coupled to high speed interface 3108. Inother implementations, multiple processors and/or multiple buses may beused, as appropriate, along with multiple memories and types of memory.Also, multiple computing devices 3100 may be connected, with each deviceproviding portions of the necessary operations (e.g., as a server bank,a group of blade servers, or a multi-processor system).

The memory 3104 stores information within the computing device 3100. Inone implementation, the memory 3104 is a volatile memory unit or units.In another implementation, the memory 3104 is a non-volatile memory unitor units. The memory 3104 may also be another form of computer-readablemedium, such as a magnetic or optical disk.

The storage device 3106 is capable of providing mass storage for thecomputing device 3100. In one implementation, the storage device 3106may be or contain a computer-readable medium, such as a floppy diskdevice, a hard disk device, an optical disk device, or a tape device, aflash memory or other similar solid state memory device, or an array ofdevices, including devices in a storage area network or otherconfigurations. A computer program product can be tangibly embodied inan information carrier. The computer program product may also containinstructions that, when executed, perform one or more methods, such asthose described above. The information carrier is a computer- ormachine-readable medium, such as the memory 3104, the storage device3106, or memory on processor 3102.

The high speed controller 3108 manages bandwidth-intensive operationsfor the computing device 3100, while the low speed controller 3112manages lower bandwidth-intensive operations. Such allocation offunctions is exemplary only. In one implementation, the high-speedcontroller 3108 is coupled to memory 3104, display 3116 (e.g., through agraphics processor or accelerator), and to high-speed expansion ports3110, which may accept various expansion cards (not shown). In theimplementation, low-speed controller 3112 is coupled to storage device3106 and low-speed expansion port 3114. The low-speed expansion port,which may include various communication ports (e.g., USB, Bluetooth,Ethernet, wireless Ethernet) may be coupled to one or more input/outputdevices, such as a keyboard, a pointing device, a scanner, or anetworking device such as a switch or router, e.g., through a networkadapter.

The computing device 3100 may be implemented in a number of differentforms, as shown in the figure. For example, it may be implemented as astandard server 3120, or multiple times in a group of such servers. Itmay also be implemented as part of a rack server system 3124. Inaddition, it may be implemented in a personal computer such as a laptopcomputer 3122. Alternatively, components from computing device 3100 maybe combined with other components in a mobile device (not shown), suchas device 3150. Each of such devices may contain one or more ofcomputing device 3100, 3150, and an entire system may be made up ofmultiple computing devices 3100, 3150 communicating with each other.

Computing device 3150 includes a processor 3152, memory 3164, aninput/output device such as a display 3154, a communication interface3166, and a transceiver 3168, among other components. The device 3150may also be provided with a storage device, such as a microdrive orother device, to provide additional storage. Each of the components3150, 3152, 3164, 3154, 3166, and 3168, are interconnected using variousbuses, and several of the components may be mounted on a commonmotherboard or in other manners as appropriate.

The processor 3152 can execute instructions within the computing device3150, including instructions stored in the memory 3164. The processormay be implemented as a chipset of chips that include separate andmultiple analog and digital processors. Additionally, the processor maybe implemented using any of a number of architectures. For example, theprocessor 410 may be a CISC (Complex Instruction Set Computers)processor, a RISC (Reduced Instruction Set Computer) processor, or aMISC (Minimal Instruction Set Computer) processor. The processor mayprovide, for example, for coordination of the other components of thedevice 3150, such as control of user interfaces, applications run bydevice 3150, and wireless communication by device 3150.

Processor 3152 may communicate with a user through control interface3158 and display interface 3156 coupled to a display 3154. The display3154 may be, for example, a TFT (Thin-Film-Transistor Liquid CrystalDisplay) display or an OLED (Organic Light Emitting Diode) display, orother appropriate display technology. The display interface 3156 maycomprise appropriate circuitry for driving the display 3154 to presentgraphical and other information to a user. The control interface 3158may receive commands from a user and convert them for submission to theprocessor 3152. In addition, an external interface 3162 may be providein communication with processor 3152, so as to enable near areacommunication of device 3150 with other devices. External interface 3162may provide, for example, for wired communication in someimplementations, or for wireless communication in other implementations,and multiple interfaces may also be used.

The memory 3164 stores information within the computing device 3150. Thememory 3164 can be implemented as one or more of a computer-readablemedium or media, a volatile memory unit or units, or a non-volatilememory unit or units. Expansion memory 3174 may also be provided andconnected to device 3150 through expansion interface 3172, which mayinclude, for example, a SIMM (Single In Line Memory Module) cardinterface. Such expansion memory 3174 may provide extra storage spacefor device 3150, or may also store applications or other information fordevice 3150. Specifically, expansion memory 3174 may includeinstructions to carry out or supplement the processes described above,and may include secure information also. Thus, for example, expansionmemory 3174 may be provide as a security module for device 3150, and maybe programmed with instructions that permit secure use of device 3150.In addition, secure applications may be provided via the SIMM cards,along with additional information, such as placing identifyinginformation on the SIMM card in a non-hackable manner.

The memory may include, for example, flash memory and/or NVRAM memory,as discussed below. In one implementation, a computer program product istangibly embodied in an information carrier. The computer programproduct contains instructions that, when executed, perform one or moremethods, such as those described above. The information carrier is acomputer- or machine-readable medium, such as the memory 3164, expansionmemory 3174, or memory on processor 3152 that may be received, forexample, over transceiver 3168 or external interface 3162.

Device 3150 may communicate wirelessly through communication interface3166, which may include digital signal processing circuitry wherenecessary. Communication interface 3166 may provide for communicationsunder various modes or protocols, such as GSM voice calls, SMS, EMS, orMMS messaging, CDMA, TDMA, PDC, WCDMA, CDMA2000, or GPRS, among others.Such communication may occur, for example, through radio-frequencytransceiver 3168. In addition, short-range communication may occur, suchas using a Bluetooth, WiFi, or other such transceiver (not shown). Inaddition, GPS (Global Positioning System) receiver module 3170 mayprovide additional navigation- and location-related wireless data todevice 3150, which may be used as appropriate by applications running ondevice 3150.

Device 3150 may also communicate audibly using audio codec 3160, whichmay receive spoken information from a user and convert it to usabledigital information. Audio codec 3160 may likewise generate audiblesound for a user, such as through a speaker, e.g., in a handset ofdevice 3150. Such sound may include sound from voice telephone calls,may include recorded sound (e.g., voice messages, music files, etc.) andmay also include sound generated by applications operating on device3150.

The computing device 3150 may be implemented in a number of differentforms, as shown in the figure. For example, it may be implemented as acellular telephone 3180. It may also be implemented as part of asmartphone 3182, personal digital assistant, or other similar mobiledevice.

Various implementations of the systems and techniques described here canbe realized in digital electronic circuitry, integrated circuitry,specially designed ASICs (application specific integrated circuits),computer hardware, firmware, software, and/or combinations thereof.These various implementations can include implementation in one or morecomputer programs that are executable and/or interpretable on aprogrammable system including at least one programmable processor, whichmay be special or general purpose, coupled to receive data andinstructions from, and to transmit data and instructions to, a storagesystem, at least one input device, and at least one output device.

These computer programs (also known as programs, software, softwareapplications or code) include machine instructions for a programmableprocessor, and can be implemented in a high-level procedural and/orobject-oriented programming language, and/or in assembly/machinelanguage. As used herein, the terms “machine-readable medium”“computer-readable medium” refers to any computer program product,apparatus and/or device (e.g., magnetic discs, optical disks, memory,Programmable Logic Devices (PLDs)) used to provide machine instructionsand/or data to a programmable processor, including a machine-readablemedium that receives machine instructions as a machine-readable signal.The term “machine-readable signal” refers to any signal used to providemachine instructions and/or data to a programmable processor.

To provide for interaction with a user, the systems and techniquesdescribed here can be implemented on a computer having a display device(e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor)for displaying information to the user and a keyboard and a pointingdevice (e.g., a mouse or a trackball) by which the user can provideinput to the computer. Other kinds of devices can be used to provide forinteraction with a user as well; for example, feedback provided to theuser can be any form of sensory feedback (e.g., visual feedback,auditory feedback, or tactile feedback); and input from the user can bereceived in any form, including acoustic, speech, or tactile input.

The systems and techniques described here can be implemented in acomputing system that includes a back end component (e.g., as a dataserver), or that includes a middleware component (e.g., an applicationserver), or that includes a front end component (e.g., a client computerhaving a graphical user interface or a Web browser through which a usercan interact with an implementation of the systems and techniquesdescribed here), or any combination of such back end, middleware, orfront end components. The components of the system can be interconnectedby any form or medium of digital data communication (e.g., acommunication network). Examples of communication networks include alocal area network (“LAN”), a wide area network (“WAN”), peer-to-peernetworks (having ad-hoc or static members), grid computinginfrastructures, and the Internet.

The computing system can include clients and servers. A client andserver are generally remote from each other and typically interactthrough a communication network. The relationship of client and serverarises by virtue of computer programs running on the respectivecomputers and having a client-server relationship to each other.

Although a few implementations have been described in detail above,other modifications are possible. Moreover, other mechanisms forgenerating and processing columnar storage representations of nestedrecords may be used. In addition, the logic flows depicted in thefigures do not require the particular order shown, or sequential order,to achieve desirable results. Other steps may be provided, or steps maybe eliminated, from the described flows, and other components may beadded to, or removed from, the described systems. Accordingly, otherimplementations are within the scope of the disclosed systems.

As additional description to the implementations described above, thepresent disclosure describes a first set of the followingimplementations:

Implementation 1 is a computer-implemented method. The method comprisesreceiving, by a computing system and from a remote computing device, afirst request to insert one or more first data values into a firstdatabase table. The method comprises identifying, by the computingsystem, that first data stored by the first database table is stored ina first logical partition of a logical collection of data, wherein: (i)the logical collection of data is designated for replication amongmultiple data centers such that a copy of the logical collection of datais designated to be stored by each of the multiple data centers, (ii)the logical collection of data is logically partitioned into multiplelogical partitions which together comprise the logical collection ofdata, and (iii) the first logical partition is one of the multiplelogical partitions of data. The method comprises identifying, by thecomputing system, that a first data center of the multiple data centersis designated as one data center, of the multiple data centers, thatinitially writes to the first logical partition of data. The methodcomprises sending, by the computing system and to the first data center,the first request to insert the one or more first data values into thefirst database table. The method comprises receiving, by the computingsystem and from the remote device, a second request to insert one ormore second data values into a second database table. The methodcomprises identifying, by the computing system, that second data storedby the second database table is stored in a second logical partition ofthe logical collection of data, wherein the second logical partition isone of the multiple logical partitions of data. The method comprisesidentifying, by the computing system, that a second data center of themultiple data centers is designated as one data center, of the multipledata centers, that initially writes to the second logical partition ofdata. The method comprises sending, by the computing system and to thesecond data center, the second request to insert the one or more seconddata values into the second database table.

Implementation 2 is the method of implementation 1, wherein the firstdata center comprises over fifty computer processors; and the seconddata center comprises over fifty computer processors.

Implementation 3 is the method of implementation 1, where the methodfurther comprises receiving, by the computing system and from the remotedevice, a third request to insert one or more third data values into athird database table. The method further comprises identifying, by thecomputing system, that third data stored by the third database table isstored in a third logical partition of the logical collection of data,wherein the third logical partition is one of the multiple logicalpartitions of data. The method further comprises identifying, by thecomputing system, that a third data center of the multiple data centersis designated as a data center that initially writes to the thirdlogical partition of data. The method further comprises sending, by thecomputing system and to the third data center, the third request toinsert one or more third data values into the third record of the thirddatabase table.

Implementation 4 is the method of implementation 1, wherein the methodfurther comprises inserting, by the first data center, the one or morefirst data values into the first database table. The method furthercomprises replicating the one or more first data values from the firstdata center to each of the multiple data centers other than the firstdata center, including the second data center. The method furthercomprises inserting, by the second data center, the one or more seconddata values into the second database table. The method further comprisesreplicating the one or more second data values from the second datacenter to each of the multiple data centers other than the second datacenter, including the first data center.

Implementation 5 is the method of implementation 4, wherein the one ormore first data values are replicated after the first data center hasinserted the one or more first data values into the first databasetable. The one or more second data values are replicated after thesecond data center has inserted the one or more second data values intothe second database table.

Implementation 6 is the method of implementation 4, wherein the firstrequest is received by the computing system before the replicating ofthe one or more first data values. The second request is received by thecomputing system before the replicating of the one or more second datavalues.

Implementation 7 is the method of implementation 4, wherein the methodfurther comprises receiving, by the first data center, the one or moresecond data values as having been replicated from the second datacenter. The method further comprises inserting, by the first datacenter, the one or more second data values into the second databasetable. The method further comprises receiving, by the second datacenter, the one or more first data values as having been replicated fromthe first data center. The method further comprises inserting, by thesecond data center, the one or more first data values into the firstdatabase table.

Implementation 8 is the method of implementation 4, wherein: inserting,by the first data center, the one or more first data values into thefirst database table includes inserting the one or more first datavalues into a copy of the first database table stored by the first datacenter; inserting, by the second data center, the one or more seconddata values into the second database table includes inserting the one ormore second data values into a copy of the second database table storedby the second data center; inserting, by the first data center, the oneor more second data values into the second database table includesinserting the one or more second data values into a copy of the seconddatabase table stored by the first data center; and inserting, by thesecond data center, the one or more first data values into the firstdatabase table includes inserting the one or more first data values intoa copy of the first database table stored by the second data center.

Implementation 9 is the method of implementation 1, wherein: the firstdata stored by the first database table is columnar data arranged forquerying by a columnar database querying system; and the second datastored by the second database table is columnar data arranged forquerying by the columnar database querying system.

Implementation 10 is the method of implementation 1, wherein the methodfurther comprises receiving, by the computing system and from the remotecomputing device, a first query to select data from the first databasetable. The method further comprises sending, by the computing system,the first query to the second data center for selection of data that isresponsive to the first query from a copy of the first database tablestored by the second data center.

Implementation 11 is the method of implementation 1, wherein: the firstdata center is an only of the multiple data centers that is designatedas initially writing to the first logical partition of data; and thesecond data center is an only of the multiple data centers that isdesignated as initially writing to the second logical partition of data.

Implementation 12 is the method of implementation 1, wherein: all datastored by the first database table is stored within the first logicalpartition; and all data stored by the second database table is storedwithin the second logical partition.

Implementation 13 is the method of implementation 1, wherein one of themultiple data centers comprises the computing system.

Implementation 14 is the method of implementation 1, wherein the firstdata center is designated as an only data center of the multiple datacenters to write to the first logical partition of data concurrently asthe second data center is designated as an only data center of themultiple data centers to write to the second logical partition of data.

Implementation 15 is directed to a recordable media having instructionsstored thereon, the instructions, when executed by one or moreprocessors, perform actions according the method of any one ofimplementations 1 to 14.

Implementation 16 is directed to a system including a recordable mediahaving instructions stored thereon, the instructions, when executed byone or more processors, perform actions according the method of any oneof implementations 1 to 14.

Implementation 17 is directed to a computer-implemented system. Thesystem comprises one or more computer processors. The system comprisesone or more computer-readable devices storing instructions that, whenexecuted by the one or more computer processors, cause performance ofoperations. The operations include receiving, by a computing system andfrom a remote computing device, a first request to insert one or morefirst data values into a first database table. The operations includeidentifying, by the computing system, that first data stored by thefirst database table is stored in a first logical partition of a logicalcollection of data. The logical collection of data is designated forreplication among multiple data centers such that a copy of the logicalcollection of data is designated to be stored by each of the multipledata centers. The logical collection of data is logically partitionedinto multiple logical partitions which together comprise the logicalcollection of data. The first logical partition is one of the multiplelogical partitions of data. The operations include identifying, by thecomputing system, that a first data center of the multiple data centersis designated as one data center, of the multiple data centers, thatinitially writes to the first logical partition of data. The operationsinclude sending, by the computing system and to the first data center,the first request to insert the one or more first data values into thefirst database table. The operations include receiving, by the computingsystem and from the remote device, a second request to insert one ormore second data values into a second database table. The operationsinclude identifying, by the computing system, that second data stored bythe second database table is stored in a second logical partition of thelogical collection of data, wherein the second logical partition is oneof the multiple logical partitions of data. The operations includeidentifying, by the computing system, that a second data center of themultiple data centers is designated as one data center, of the multipledata centers, that initially writes to the second logical partition ofdata. The operations include sending, by the computing system and to thesecond data center, the second request to insert the one or more seconddata values into the second database table.

Implementation 18 is directed to a computer-implemented method. Themethod comprises receiving, by a computing system and from a remotecomputing device, a first request to insert one or more first datavalues into a first database table. The method comprises identifying, bythe computing system, that first data stored by the first database tableis stored in a first logical partition of a logical collection of data.The logical collection of data is designated for replication amongmultiple data centers such that a copy of the logical collection of datais designated to be stored by each of the multiple data centers. Each ofthe multiple data centers comprises over a fifty computer processors.The logical collection of data is logically partitioned into multiplelogical partitions which together comprise the logical collection ofdata. The first logical partition is one of the multiple logicalpartitions of data. The method comprises identifying, by the computingsystem, that a first data center of the multiple data centers isdesignated as an only one data center, of the multiple data centers,that initially writes to the first logical partition of data. The methodcomprises sending, by the computing system and to the first data center,the first request to insert the one or more first data values into thefirst database table. The method comprises inserting, by the first datacenter, the one or more first data values into a copy of the firstdatabase table stored by the first data center. The method comprisesreplicating, after the insertion of the one or more first data valuesinto the copy of the first database table stored by the first datacenter, the one or more first data values from the first data center toeach of the multiple data centers other than the first data center,including the second data center. The method comprises receiving, by asecond data center of the multiple data centers, the one or more firstdata values as having been replicated from the first data center. Themethod comprises inserting, by the second data center, the one or morefirst data values into a copy of the first database table stored by thesecond data center. The method comprises receiving, by the computingsystem and from the remote device, a second request to insert one ormore second data values into a second database table. The methodcomprises identifying, by the computing system, that second data storedby the second database table is stored in a second logical partition ofthe logical collection of data, wherein the second logical partition isone of the multiple logical partitions of data. The method comprisesidentifying, by the computing system, that the second data center isdesignated as an only one data center, of the multiple data centers,that initially writes to the second logical partition of data, whereinthe second data center is designated as an only one data center, of themultiple data centers, that initially writes to the second logicalpartition of data concurrent with the first data center being designatedas the only one data center, of the multiple data centers, thatinitially writes to the first logical partition of data. The methodcomprises sending, by the computing system and to the second datacenter, the second request to insert the one or more second data valuesinto the second database table. The method comprises inserting, by thesecond data center, the one or more second data values into a copy ofthe second database table stored by the second data center. The methodcomprises replicating, after the insertion of the one or more seconddata values into the copy of the second database table stored by thesecond data center, the one or more second data values from the seconddata center to each of the multiple data centers other than the seconddata center, including the first data center. The method comprisesreceiving, by the first data center, the one or more second data valuesas having been replicated from the second data center. The methodcomprises inserting, by the first data center, the one or more seconddata values into a copy of the second database table stored by the firstdata center.

As additional description to the implementations described above, thepresent disclosure describes a second set of the followingimplementations:

Implementation 1 is directed to a computerized system. The systemcomprises a first data center of multiple data centers, the multipledata centers configured to replicate a logical collection of data amongthe multiple data centers, wherein the logical collection of datacomprises multiple logical partitions of data that as a whole comprisethe logical collection of data, wherein the multiple logical partitionsof data comprise a first logical partition of data, a second logicalpartition of data, and a third logical partition of data. The systemcomprises a second data center of the multiple data centers. The systemcomprises a third data center of the multiple data centers. The systemcomprises a first writing subsystem, executable by computers at thefirst data center, that is designated to write updates to a copy of thefirst logical partition of data that is stored by the first data center.The system comprises a second writing subsystem, executable by computersat the second data center, that is designated to write updates to a copyof the second logical partition of data that is stored by the seconddata center. The system comprises a third writing subsystem, executableby computers at the third data center, that is designated to writeupdates to a copy of the third logical partition of data that is storedby the third data center. The system comprises a writing querydistributor, executable by one or more computers, that is configured toreceive multiple queries from multiple remote computing devices over aperiod of time, and to: (i) distribute, to the first writing subsystemat the first data center, those of the multiple queries that have beenidentified as being structured to write to the first logical partitionof data, (ii) distribute, to the second writing subsystem at the seconddata center, those of the multiple queries that have been identified asbeing structured to write to the second logical partition of data, and(iii) distribute, to the third writing subsystem at the third datacenter, those of the multiple queries that have been identified as beingstructured to write to the third logical partition of data.

Implementation 2 is the system of implementation 1, wherein: those ofthe multiple queries that have been identified as being structured towrite to the first logical partition of data are all of the multiplequeries that have been identified as being structured to write to thefirst logical partition of data; those of the multiple queries that havebeen identified as being structured to write to the second logicalpartition of data are all of the multiple queries that have beenidentified as being structured to write to the second logical partitionof data; and those of the multiple queries that have been identified asbeing structured to write to the third logical partition of data are allof the multiple queries that have been identified as being structured towrite to the third logical partition of data.

Implementation 3 is the system of implementation 1, wherein: the periodof time is at least an hour; those of the multiple queries that havebeen identified as being structured to write to the first logicalpartition of data including at least a hundred queries; those of themultiple queries that have been identified as being structured to writeto the second logical partition of data including at least a hundredqueries; and those of the multiple queries that have been identified asbeing structured to write to the third logical partition of dataincluding at least a hundred queries.

Implementation 4 is the system of implementation 1, wherein the systemfurther comprises a first replicating subsystem, executable by computersat the first data center, to replicate the updates to the first logicalpartition of data from the first data center to the second data centerand the third data center. The system further comprises a secondreplicating subsystem, executable by computers at the second datacenter, to replicate the updates to the second logical partition of datafrom the second data center to the first data center and the third datacenter. The system further comprises a third replicating subsystem,executable by computers at the third data center, to replicate theupdates to the third logical partition of data from the third datacenter to the first data center and the second data center.

Implementation 5 is the system of implementation 1, wherein the systemfurther comprises a first querying subsystem, executable by computers atthe first data center, to receive queries and, in response, to querycopies of the first logical partition of data, second logical partitionof data, and third logical partition of data that are stored by thefirst data center. The system further comprises a second queryingsubsystem, executable by computers at the second data center, to receivequeries and, in response, to query copies of the first logical partitionof data, second logical partition of data, and third logical partitionof data that are stored by the second data center. The system furthercomprises a third querying subsystem, executable by computers at thethird data center, to receive queries and, in response, to query copiesof the first logical partition of data, second logical partition ofdata, and third logical partition of data that are stored by the thirddata center.

Implementation 6 is the system of implementation 1, wherein: the firstdata center comprises at least fifty computer processors; the seconddata center comprises at least fifty computer processors; and the thirddata center comprises at least fifty computer processors.

Implementation 7 is the system of implementation 1, wherein the systemfurther comprises a reading query router, executable by at least onecomputer, to receive a reading query that is structured to select datafrom a table that is stored within the logical collection of data, andto route the reading query to one of the first data center, the seconddata center, or the third data center that is geographically closest tothe at least one computer, without regard for whether the table islogically stored within the first logical partition of data, the secondlogical partition of data, or the third logical partition of data.

Implementation 8 is the system of implementation 7, the reading query isstructured to select data from the second logical partition of data; andthe reading query router is configured to route the reading query to thefirst data center during the period of time.

Implementation 9 is the system of implementation 1, wherein one or morecomputers through which the writing query distributor is executable areone or more computers of the first data center, the second data center,or the third data center.

As additional description to the implementations described above, thepresent disclosure describes a third set of the followingimplementations:

Implementation 10 is directed to a computer-implemented method. Themethod comprises receiving, by a computing system, a query that isstructured to cause a querying system to select data from a databasetable. The method comprises identifying, by the computing system, thatthe data that is to be selected from the database table is stored withinone or more logical portions of data. The method comprises identifying,by the computing system, multiple data centers among which the one ormore logical portions of data are designated for replication. The methodcomprises identifying, by the computing system, that the one or morelogical portions of data have been fully replicated among none of themultiple data centers. The method comprises, in response to identifyingthat the one or more logical portions of data have been fully replicatedamong none of the multiple data centers, identifying, by the computingsystem, a most-recent data center of the multiple data centers thatmost-recently updated the one or more logical portions of data. Themethod comprises sending, by the computing system, the query to theidentified most-recent data center, so as to cause the identifiedmost-recent data center to select data, in accordance with the query,from a copy of the database table that is stored by the most-recent datacenter.

Implementation 11 is the method of implementation 10, whereinidentifying the most-recent data center includes identifying that awrite timestamp for a copy of the one or more logical portions of datastored by the most-recent data center is more recent than writetimestamps for copies of the one or more logical portions of data storedby other of the multiple data centers that do not include themost-recent data center.

Implementation 12 is the method of implementation 10, wherein the methodfurther comprises receiving, by the computing system, a second querythat is structured so as to cause the querying system to select seconddata from a second database table. The method further comprisesidentifying, by the computing system, that the second data that is to beselected from the second database table is stored by one or more secondlogical portions of data. The method further comprises identifying, bythe computing system, that the one or more second logical portions ofdata are designated for replication among the multiple data centers. Themethod further comprises identifying, by the computing system, that oneor more of the second logical portions of data have been fullyreplicated among at least two of the multiple data centers. The methodfurther comprises, in response to identifying that the one or more ofthe second logical portions of data have been fully replicated among atleast two of the multiple data centers, identifying, by the computingsystem, one of the at least two data centers to handle the second querybased at least in part on a first criterion. The method furthercomprises sending, by the computing system, the second query to theidentified one data center for execution of the second query.

Implementation 13 is the method of implementation 10, whereinidentifying that the one or more of the second logical portions havebeen fully replicated among at least two of the multiple data centersincludes: identifying, by the computing system, a first data center ofthe multiple data centers as being one of the at least two of themultiple data centers among which the one or more second logicalportions of data have been fully replicated, based on identifying thatthe first data center is designated as a data center that initiallyupdates a local copy of the one or more second logical portions of databefore other of the multiple data centers update respective local copiesof the one or more second logical portions of data; and identifying, bythe computing system, a second data center of the multiple data centersas being another of the at least two of the multiple data centers amongwhich the one or more second logical portions of data have been fullyreplicated, based on identifying that a copy of the one or more secondlogical portions of data that is stored by the second data center has awrite timestamp that is more recent than a write timestamp for a copy ofthe one or more second logical portions of data that is stored by thefirst data center.

Implementation 14 is the method of implementation 13, wherein thecomputing system is adapted to not select certain of the multiple datacenters as having fully replicated the one or more second logicalportions of data as a result of identifying that copies of the one ormore second logical portions of data stored by the certain data centershave write timestamps for the copies of the one or more second logicalportions of data that are less recent than a write timestamp for thecopy of the one or more second logical portions of data that is storedby the first data center.

Implementation 15 is the method of implementation 13, wherein the methodfurther comprises receiving, by the computing system, a third query thatis structured so as to select data from a third database table. Themethod further comprises identifying, by the computing system, that thethird data that is to be selected from the database table is stored bythe one or more third logical portions of data; identifying, by thecomputing system, that the one or more third logical portions of dataare designated for replication among the multiple data centers. Themethod further comprises identifying, by the computing system, that asingle one of the multiple data centers has fully replicated the logicaldata. The method further comprises, in response to identifying that asingle one of the multiple data centers has fully replicated the logicaldata, sending, by the computing system, the third query to the singleone of the multiple data centers.

Implementation 16 is the method of implementation 12, whereinidentifying one of the at least two data centers to handle the secondquery based at least in part on the first criterion includes identifyingwhich of the at least two data centers is geographically closest to aremote computing device that transmitted the second query to thecomputing system.

Implementation 17 is the method of implementation 12, whereinidentifying one of the at least two data centers to handle the secondquery based at least in part on the first criterion includes identifyingwhich of the at least two data centers is associated with a shortesttrip time for a message transmitted between (i) a remote computingdevice that transmitted the second query to the computing system, and(ii) each of the at least two data centers.

Implementation 18 is the method of implementation 12, whereinidentifying one of the at least two data centers to handle the secondquery based at least in part on the first criterion includes identifyingwhich of the at least two data centers has been determined to have amost available computing capacity from among the at least two datacenters.

Implementation 19 is directed to a recordable media having instructionsstored thereon, the instructions, when executed by one or moreprocessors, perform actions according the method of any one ofimplementations 10 to 18.

Implementation 20 is directed to a system including a recordable mediahaving instructions stored thereon, the instructions, when executed byone or more processors, perform actions according the method of any oneof implementations 10 to 18.

1-9. (canceled)
 10. A computer-implemented method, comprising:receiving, by a computing system, a query that is structured to cause aquerying system to select data from a database table; identifying, bythe computing system, that the data that is to be selected from thedatabase table is stored within one or more logical portions of data;identifying, by the computing system, multiple data centers among whichthe one or more logical portions of data are designated for replication;identifying, by the computing system, that the one or more logicalportions of data have been fully replicated among none of the multipledata centers; in response to identifying that the one or more logicalportions of data have been fully replicated among none of the multipledata centers, identifying, by the computing system, a most-recent datacenter of the multiple data centers that most-recently updated the oneor more logical portions of data; and sending, by the computing system,the query to the identified most-recent data center, so as to cause theidentified most-recent data center to select data, in accordance withthe query, from a copy of the database table that is stored by themost-recent data center.
 11. The computer-implemented method of claim10, wherein identifying the most-recent data center includes identifyingthat a write timestamp for a copy of the one or more logical portions ofdata stored by the most-recent data center is more recent than writetimestamps for copies of the one or more logical portions of data storedby other of the multiple data centers that do not include themost-recent data center.
 12. The computer-implemented method of claim10, further comprising: receiving, by the computing system, a secondquery that is structured so as to cause the querying system to selectsecond data from a second database table; identifying, by the computingsystem, that the second data that is to be selected from the seconddatabase table is stored by one or more second logical portions of data;identifying, by the computing system, that the one or more secondlogical portions of data are designated for replication among themultiple data centers; identifying, by the computing system, that one ormore of the second logical portions of data have been fully replicatedamong at least two of the multiple data centers; in response toidentifying that the one or more of the second logical portions of datahave been fully replicated among at least two of the multiple datacenters, identifying, by the computing system, one of the at least twodata centers to handle the second query based at least in part on afirst criterion; and sending, by the computing system, the second queryto the identified one data center for execution of the second query. 13.The computer-implemented method of claim 12, wherein identifying thatthe one or more of the second logical portions have been fullyreplicated among at least two of the multiple data centers includes:identifying, by the computing system, a first data center of themultiple data centers as being one of the at least two of the multipledata centers among which the one or more second logical portions of datahave been fully replicated, based on identifying that the first datacenter is designated as a data center that initially updates a localcopy of the one or more second logical portions of data before other ofthe multiple data centers update respective local copies of the one ormore second logical portions of data; and identifying, by the computingsystem, a second data center of the multiple data centers as beinganother of the at least two of the multiple data centers among which theone or more second logical portions of data have been fully replicated,based on identifying that a copy of the one or more second logicalportions of data that is stored by the second data center has a writetimestamp that is more recent than a write timestamp for a copy of theone or more second logical portions of data that is stored by the firstdata center.
 14. The computer-implemented method of claim 13, whereinthe computing system is adapted to not select certain of the multipledata centers as having fully replicated the one or more second logicalportions of data as a result of identifying that copies of the one ormore second logical portions of data stored by the certain data centershave write timestamps for the copies of the one or more second logicalportions of data that are less recent than a write timestamp for thecopy of the one or more second logical portions of data that is storedby the first data center.
 15. The computer-implemented method of claim13, further comprising: receiving, by the computing system, a thirdquery that is structured so as to select data from a third databasetable; identifying, by the computing system, that the third data that isto be selected from the database table is stored by the one or morethird logical portions of data; identifying, by the computing system,that the one or more third logical portions of data are designated forreplication among the multiple data centers; identifying, by thecomputing system, that a single one of the multiple data centers hasfully replicated the logical data; and in response to identifying that asingle one of the multiple data centers has fully replicated the logicaldata, sending, by the computing system, the third query to the singleone of the multiple data centers.
 16. The computer-implemented method ofclaim 12, wherein identifying one of the at least two data centers tohandle the second query based at least in part on the first criterionincludes identifying which of the at least two data centers isgeographically closest to a remote computing device that transmitted thesecond query to the computing system.
 17. The computer-implementedmethod of claim 12, wherein identifying one of the at least two datacenters to handle the second query based at least in part on the firstcriterion includes identifying which of the at least two data centers isassociated with a shortest trip time for a message transmitted between(i) a remote computing device that transmitted the second query to thecomputing system, and (ii) each of the at least two data centers. 18.The computer-implemented method of claim 12, wherein identifying one ofthe at least two data centers to handle the second query based at leastin part on the first criterion includes identifying which of the atleast two data centers has been determined to have a most availablecomputing capacity from among the at least two data centers.
 19. One ormore computer-readable devices including instructions that, whenexecuted by one or more processors, cause performance of operations thatcomprise: receiving, by a computing system, a query that is structuredto cause a querying system to select data from a database table;identifying, by the computing system, that the data that is to beselected from the database table is stored within one or more logicalportions of data; identifying, by the computing system, multiple datacenters among which the one or more logical portions of data aredesignated for replication; identifying, by the computing system, thatthe one or more logical portions of data have been fully replicatedamong none of the multiple data centers; in response to identifying thatthe one or more logical portions of data have been fully replicatedamong none of the multiple data centers, identifying, by the computingsystem, a most-recent data center of the multiple data centers thatmost-recently updated the one or more logical portions of data; andsending, by the computing system, the query to the identifiedmost-recent data center, so as to cause the identified most-recent datacenter to select data, in accordance with the query, from a copy of thedatabase table that is stored by the most-recent data center.
 20. Theone or more computer-readable devices of claim 19, wherein identifyingthe most-recent data center includes identifying that a write timestampfor a copy of the one or more logical portions of data stored by themost-recent data center is more recent than write timestamps for copiesof the one or more logical portions of data stored by other of themultiple data centers that do not include the most-recent data center.21. The one or more computer-readable devices of claim 19, wherein theoperations further comprise: receiving, by the computing system, asecond query that is structured so as to cause the querying system toselect second data from a second database table; identifying, by thecomputing system, that the second data that is to be selected from thesecond database table is stored by one or more second logical portionsof data; identifying, by the computing system, that the one or moresecond logical portions of data are designated for replication among themultiple data centers; identifying, by the computing system, that one ormore of the second logical portions of data have been fully replicatedamong at least two of the multiple data centers; in response toidentifying that the one or more of the second logical portions of datahave been fully replicated among at least two of the multiple datacenters, identifying, by the computing system, one of the at least twodata centers to handle the second query based at least in part on afirst criterion; and sending, by the computing system, the second queryto the identified one data center for execution of the second query. 22.The one or more computer-readable devices of claim 21, whereinidentifying that the one or more of the second logical portions havebeen fully replicated among at least two of the multiple data centersincludes: identifying, by the computing system, a first data center ofthe multiple data centers as being one of the at least two of themultiple data centers among which the one or more second logicalportions of data have been fully replicated, based on identifying thatthe first data center is designated as a data center that initiallyupdates a local copy of the one or more second logical portions of databefore other of the multiple data centers update respective local copiesof the one or more second logical portions of data; and identifying, bythe computing system, a second data center of the multiple data centersas being another of the at least two of the multiple data centers amongwhich the one or more second logical portions of data have been fullyreplicated, based on identifying that a copy of the one or more secondlogical portions of data that is stored by the second data center has awrite timestamp that is more recent than a write timestamp for a copy ofthe one or more second logical portions of data that is stored by thefirst data center.
 23. The one or more computer-readable devices ofclaim 22, wherein the computing system is adapted to not select certainof the multiple data centers as having fully replicated the one or moresecond logical portions of data as a result of identifying that copiesof the one or more second logical portions of data stored by the certaindata centers have write timestamps for the copies of the one or moresecond logical portions of data that are less recent than a writetimestamp for the copy of the one or more second logical portions ofdata that is stored by the first data center.
 24. The one or morecomputer-readable devices of claim 22, wherein the operations furthercomprise: receiving, by the computing system, a third query that isstructured so as to select data from a third database table;identifying, by the computing system, that the third data that is to beselected from the database table is stored by the one or more thirdlogical portions of data; identifying, by the computing system, that theone or more third logical portions of data are designated forreplication among the multiple data centers; identifying, by thecomputing system, that a single one of the multiple data centers hasfully replicated the logical data; and in response to identifying that asingle one of the multiple data centers has fully replicated the logicaldata, sending, by the computing system, the third query to the singleone of the multiple data centers.
 25. The one or more computer-readabledevices of claim 21, wherein identifying one of the at least two datacenters to handle the second query based at least in part on the firstcriterion includes identifying which of the at least two data centers isgeographically closest to a remote computing device that transmitted thesecond query to the computing system.
 26. The one or morecomputer-readable devices of claim 21, wherein identifying one of the atleast two data centers to handle the second query based at least in parton the first criterion includes identifying which of the at least twodata centers is associated with a shortest trip time for a messagetransmitted between (i) a remote computing device that transmitted thesecond query to the computing system, and (ii) each of the at least twodata centers.
 27. The one or more computer-readable devices of claim 21,wherein identifying one of the at least two data centers to handle thesecond query based at least in part on the first criterion includesidentifying which of the at least two data centers has been determinedto have a most available computing capacity from among the at least twodata centers.